SQL Server Analysis Services database
Power Query is a powerful data connectivity and transformation tool used in Power BI. It is capable of connecting to various data sources, including SQL Server Analysis Services (SSAS) database. In this article, we will explore how to use Power Query’s M language code to connect to an SSAS data source from inside Power BI.
Understanding the M Language in Power Query
Power Query uses the M language to perform data transformations and manipulations. The M language is a functional programming language that is used to define queries and transformations. It is a powerful language that allows for complex data manipulations, and it is easy to learn.
M language is case-sensitive, and each statement must end with a semicolon. Power Query’s M language code is generated automatically when we use the Power Query Editor to perform transformations in Power BI.
Connecting to SQL Server Analysis Services Data Source
To connect to an SSAS data source, we need to use the M language code in Power Query. We can use the “AnalysisServices.Database” function to connect to the SSAS data source. Here’s an example of the M language code to connect to an SSAS data source:
let
Source = AnalysisServices.Database(“serverName”, “databaseName”)
in
Source
In the above code, “serverName” is the name of the SQL Server Analysis Services server, and “databaseName” is the name of the SSAS database. Once we execute this code, we will be prompted to enter our Windows credentials to connect to the SSAS data source.
Querying Data from SSAS Data Source
Once we have established a connection with the SSAS data source, we can use the M language code to retrieve data from it. We can use the “AnalysisServices.Databases” function to retrieve a list of all available databases in the SSAS server. Here’s an example of the M language code to retrieve a list of available databases:
let
Source = AnalysisServices.Databases(“serverName”),
#”Filtered Rows” = Table.SelectRows(Source, each ([Name] = “databaseName”)),
#”Expanded Content” = Table.ExpandTableColumn(#”Filtered Rows”, “Content”, {“Name”, “ID”, “Description”}, {“Name”, “ID”, “Description”})
in
#”Expanded Content”
In the above code, we first retrieve a list of all databases in the SSAS server using the “AnalysisServices.Databases” function. We then filter the results to only include the database we are interested in, using the “Table.SelectRows” function. Finally, we expand the “Content” column to include the name, ID, and description of the selected database, using the “Table.ExpandTableColumn” function.
Conclusion
Power Query’s M language code is a powerful tool for connecting to and querying data from an SSAS data source in Power BI. In this article, we explored how to use the “AnalysisServices.Database” and “AnalysisServices.Databases” functions to connect to and retrieve data from an SSAS data source. With this knowledge, you can now easily connect to and analyze data from your SSAS database in Power BI.
