AnalysisServices.Databases

Introduction to AnalysisServices.Databases

AnalysisServices.Databases is a Power Query M function that is used to retrieve a list of databases from a Microsoft SQL Server Analysis Services server. This function requires the following parameters:

– server: The name of the Analysis Services server.

– optional options: A record containing optional parameters for the function.

The AnalysisServices.Databases function returns a table that contains the following columns:

– Name: The name of the database.

– ID: The ID of the database.

– Description: A description of the database.

– Created: The date and time when the database was created.

– LastProcessed: The date and time when the database was last processed.

The M Code Behind AnalysisServices.Databases

The M code behind the AnalysisServices.Databases function is relatively complex, but it can be broken down into several parts. Let’s take a closer look at each part:

Part 1: Connection to Analysis Services Server

The first part of the M code establishes a connection to the Analysis Services server using the server parameter. This is done using the following code:

This code creates a new data source using the AnalysisServices.Database function and passes the server parameter. The resulting data source is stored in the Source variable.

Part 2: Retrieve List of Databases

The next part of the M code retrieves a list of databases from the Analysis Services server. This is done using the following code:

This code retrieves a table called Databases from the Source data source and stores it in the dbs variable. The table is then filtered to only include the Name, ID, Description, Created, and LastProcessed columns using the Table.SelectColumns function.

Part 3: Optional Parameters

The final part of the M code deals with the optional parameters that can be passed to the AnalysisServices.Databases function. This is done using the following code:

This code checks if the options parameter is null. If it is, a default Timeout value of 180 seconds is assigned to options. The CommandTimeout parameter is then set to the value of options[Timeout].

In conclusion, the AnalysisServices.Databases function is a powerful tool for retrieving a list of databases from an Analysis Services server. The M code behind this function is relatively complex, but it can be broken down into several parts, including the connection to the server, the retrieval of the database list, and the optional parameters. By understanding the M code behind this function, users can gain a deeper understanding of how Power Query works and how they can use it to analyze and transform their data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

20-22 Nov 23 (London or Online)

Contact Us