Cube.Properties

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Cube.Properties

Cube.Properties is a powerful function that allows you to retrieve metadata about a cube or a tabular model. It can be used to retrieve information about the structure of the model, the data sources, and the relationships between tables. In this article, we will explore the M code behind the Cube.Properties function and show you how to use it to retrieve useful information about your data model.

What is the Cube.Properties Function?

Before we dive into the M code behind the Cube.Properties function, let’s first understand what it does. The Cube.Properties function is used to retrieve metadata about a cube or a tabular model. It takes two arguments: the server name and the cube name. Here is the syntax of the Cube.Properties function:

Cube.Properties(server as text, cube as text) as record

The function returns a record that contains various properties of the cube or tabular model. These properties include the name of the cube, the data sources used in the model, the tables and columns in the model, and the relationships between the tables.

Understanding the M Code behind Cube.Properties

Now that we know what the Cube.Properties function does, let's take a look at the M code behind it. The Cube.Properties function is actually a combination of several other M functions that work together to retrieve the metadata about the cube or tabular model.

Here are the M functions that make up the Cube.Properties function:

- `AnalysisServices.Database`: This function connects to the Analysis Services database and retrieves the metadata about the cube or tabular model.

- `AnalysisServices.Cube`: This function retrieves the metadata about the cube or tabular model.

- `AnalysisServices.DataSources`: This function retrieves the data sources used in the cube or tabular model.

- `AnalysisServices.Tables`: This function retrieves the tables in the cube or tabular model.

- `AnalysisServices.Columns`: This function retrieves the columns in the cube or tabular model.

- `AnalysisServices.Relationships`: This function retrieves the relationships between the tables in the cube or tabular model.

Each of these functions is used to retrieve a specific type of metadata about the cube or tabular model. The Cube.Properties function combines the results of these functions into a single record that contains all of the metadata.

How to Use Cube.Properties to Retrieve Metadata

Now that we understand the M code behind the Cube.Properties function, let's take a look at how to use it to retrieve metadata about our cube or tabular model.

To use the Cube.Properties function, we first need to create a new query in Power Query. Here are the steps to create a new query:

1. Open Excel or Power BI.

2. Go to the Data tab and click on "From Other Sources".

3. Click on "Blank Query" to create a new query.

4. In the new query window, click on "Advanced Editor".

5. In the Advanced Editor window, paste the following M code:

let

Source = Cube.Properties(“localhost”, “Adventure Works DW”),

#”Expanded DataSources” = Table.ExpandTableColumn(Source, “DataSources”, {“DataSourceID”, “DataSourceName”, “ConnectionString”}, {“DataSources.DataSourceID”, “DataSources.DataSourceName”, “DataSources.ConnectionString”}),

#”Expanded Tables” = Table.ExpandTableColumn(#”Expanded DataSources”, “Tables”, {“TableName”, “Columns”}, {“Tables.TableName”, “Tables.Columns”}),

#”Expanded Columns” = Table.ExpandTableColumn(#”Expanded Tables”, “Tables.Columns”, {“ColumnName”, “DataType”, “DataSourceID”, “DataSourceName”, “TableName”, “TableID”}, {“Tables.Columns.ColumnName”, “Tables.Columns.DataType”, “Tables.Columns.DataSourceID”, “Tables.Columns.DataSourceName”, “Tables.Columns.TableName”, “Tables.Columns.TableID”}),

#”Expanded Relationships” = Table.ExpandTableColumn(#”Expanded Columns”, “Relationships”, {“FromTableName”, “FromColumnName”, “ToTableName”, “ToColumnName”, “RelationshipType”}, {“Relationships.FromTableName”, “Relationships.FromColumnName”, “Relationships.ToTableName”, “Relationships.ToColumnName”, “Relationships.RelationshipType”})

in

#”Expanded Relationships”

This M code retrieves the metadata about the "Adventure Works DW" cube on the "localhost" server. You can modify this code to retrieve the metadata about your own cube or tabular model.

Once you have pasted the M code into the Advanced Editor window, click on "Done" to close the window. The query will now execute and retrieve the metadata about the cube or tabular model.

Understanding the Output of Cube.Properties

The output of the Cube.Properties function is a record that contains various properties of the cube or tabular model. These properties are grouped into several tables, as shown below:

- DataSources: This table contains information about the data sources used in the cube or tabular model, including the data source ID, name, and connection string.

- Tables: This table contains information about the tables in the cube or tabular model, including the table name, columns, and table ID.

- Columns: This table contains information about the columns in the cube or tabular model, including the column name, data type, and table ID.

- Relationships: This table contains information about the relationships between the tables in the cube or tabular model, including the from table name, from column name, to table name, to column name, and relationship type.

You can use these tables to retrieve specific information about your cube or tabular model. For example, you can use the DataSources table to retrieve the connection string for a particular data source, or you can use the Tables table to retrieve the columns for a particular table.

The Cube.Properties function is a powerful tool for retrieving metadata about a cube or tabular model in Power Query. By understanding the M code behind this function, you can customize it to retrieve specific information about your own data model. Whether you are a business analyst, a data scientist, or a data engineer, the Cube.Properties function can help you to better understand the structure and relationships of your data.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)