Using DAX Studio To Jump Start Your Data Dictionary

Using DAX Studio To Jump Start Your Data Dictionary 

In the world of analytics, understanding and being able to define your data is king in any data project. Organizations often focus on the early stage around getting systems set up and the technology behind the data, but they easily lose focus on the metrics and key information the business needs and the process to govern their data. 

Several businesses start off slowly during their adoption to the cloud and Power BI. Often, they create metrics and reports a la carte. Over time, the inventory of reports measures and metrics grow, and it becomes hard for IT and the business to communicate on a common set of terms. When these scenarios arise there becomes a need to have a data dictionary that stores all existing measures and how they are calculated. However, this poses a challenge because Power BI does not natively have a way to export the list of metrics. 

However, there are still ways of extracting key metrics in Power BI to help jump start your data dictionary.  One way is to use an open source tool called DAX Studio. The tool offers a lot more than simply being able to extract the metrics. The focus of this blog  is how to use this tool to extract the information in Power BI.  DAX Studio lets users run a query to export all measured information stored in Power BI.  

To begin:   

Start by downloading and installing DAX Studio.  The download can be accessed below: 

DAX Studio - The ultimate client tool for working with DAX queries 

Click on the install and confirm that the program was installed correctly. 

  After opening DAX Studio, you will notice the following interface: 

Next, we will look at the the DAX Studio query window. Note, this assumes that you have a Power BI data model to work with. 

After installing DAX Studio and going to the query window, enter the following query: 

This will output the following information and capture the name of the measure and folder and tables where measure is stored in Power BI:  

Notice that the output has all the key information we would need for the name of the measure, folder, the calculations and more. 

To move the query into a workable Excel format, select the output and set static. This will allow the tool to export the query to Excel. 

After you have selected the output, make sure the query is entered in the window as seen below: 

Then, click on the Run button to output the data to Excel: 

Select your folder path and then click Save. 

Think of the export as the start of your data dictionary journey as it captures the key metrics. Defining what each metric is and who own the definition is all part of the governance life cycle which will be discussed in future posts.  After levering the data, you will have the key first step in jump starting your data dictionary.