Have you ever wanted to run an example of a function to refresh your memory on the value returned, or the function’s behavior and/or input parameters? Are you interested in extending your knowledge of a primary MSBI project language, such as DAX, Power Query (M), or T-SQL, beyond the core concepts and most common functions? In either scenario, and particularly if you’re a BI developer, you may consider a BI function reference solution as described in this post as a valuable long term resource.

This post describes the essential steps to build code sample solutions for DAX, Power Query (M), and T-SQL via SQL Server Management Studio (SSMS) and Power BI Desktop. Additionally, supporting tips are provided on maintaining and incrementally enhancing the solution.

Function Reference Examples

To give an idea of what this might look like (and whether you should keep reading), the following images contain the essential structure of the function reference solutions:

This slideshow requires JavaScript.

Per the above images, separate SQL Server Management Studio solutions are used for both the DAX and the T-SQL function reference. Each solution (T-SQL or DAX), contains multiple projects reflecting a specific category of functions. Each project contains many individual query files (.msdax or .sql), with one query file for each function.

For example, the T-SQL solution contains an Analytic project containing SQL query files for the LAG, LEAD, and other functions as documented in MS Docs. Likewise, the DAX solution contains an Other Functions project containing DAX query files for functions such as INTERSECT and NATURALLEFTOUTERJOIN as documented on MSDN.

For the Power Query (M) functions, although other options are available such as using the Power Query SDK in Visual Studio for storing separate .pq files, one Power BI Desktop file (.pbix) is defined for each of the main function categories. The queries in each PBIX file are organized into folders representing the groups of functions as documented on MSDN.

With sample databases available and sample queries created against these sources for each individual query file (or M query) – which obviously takes time, the user has a very robust and easy-to-use personal reference to the functions of the given language. In short, the “how the does that function work again?” question can be answered very quickly as an actual query, likely with the user’s own comments and based on familiar data, is readily accessible.

Is this really necessary or worth the effort?

A few questions you might ask before committing time to this:

1. But aren’t examples of queries included in the function documentation (linked above)?
   • Yes, but viewing someone’s code or reading about a concept does not deliver the same learning experience as creating your own examples. Ownership of an asset, such as function library in this case, increases one’s attachment to the asset which ultimately leads to a deeper understanding.
2. But why should I write DAX queries if I only write DAX measures?
   • All concepts of DAX (e.g. row and filter context, context transition) apply to both queries and measures.
   • Additionally, many of the table functions used in DAX queries can leveraged in DAX measures.
3. Why Power Query (M)?
   • The use cases for Power Query (M) are growing beyond Excel, Power BI Desktop, and Analysis Services Tabular. Power Query is used in dataflows, MS Flow soon, and possibly paginated reports in the future as mentioned at the Business Applications Summit.
   • Intellisense for M queries is expected near October 2018 per the release notes.
4. What about DAX Studio?
   • The concept of solutions and projects of multiple query files is not supported.
   • You can still open the .msdax files from DAX Studio, execute the queries against your Analysis Services instance and save changes if necessary.
     • SSMS supports Intellisense for DAX but of course DAX Studio provides many more features too.
5. Will this become outdated?
   • No, languages change much less quickly than product features (e.g. Bookmarks) and these languages are built into the current roadmap.
     • Many functions have already been added to DAX and M over the past 2-3 years making the languages fairly mature.
   • Even if a new analytical or query language was introduced soon, it would likely take years for the language to mature and for companies to adopt it as was the case with DAX several years ago.

Obviously not all functions require the same attention but, if MSBI and Power BI solution development represents a significant portion of your work, then building up these function libraries (over time) could be a great investment. At a minimum the function libraries help you appreciate the power and flexibility of these languages and the volume of queries to create will very likely help generate new ideas and approaches.

Step 1: Development Server and SSMS

The DAX and T-SQL solutions in this approach use connections to a Developer edition of SQL Server 2017 which you can download and install for free. Connections to a SQL Server Analysis Services (SSAS) Tabular instance and to a database engine instance will be used be the the DAX and T-SQL solutions in SQL Server Management Studio, respectively. You can download and install SSMS and SQL Server Data Tools (SSDT) for free as well. You’ll need SSDT to build and deploy the Tabular model in Step #4.

This step and possibly the next step (sample database) may not be necessary if you already have access to a development server at your organization. Building a code sample solution with company resources may provide a more visible sign of your skill development and make the example queries more meaningful. However, in the event that you leave the organization, at best you’ll be able to keep the query code (maybe) – you won’t have a source database to run them against. For this reason, along with other risks such as modifications to the queries, source database, or development server by others, I’d recommend keeping everything on your own machine.

Step 2: Sample Relational Database

With your development server and SSMS installed, you now need a sample relational database which will directly support your T-SQL solution and indirectly your DAX solution as this will be the source for an Analysis Services Tabular model. You can download the AdventureWorks data warehouse sample database here and then restore the backup file in SSMS via these instructions.

Alternatively, you could download and restore the Wide World Importers data warehouse database. The following blog post recommends WWI rather than Adventure Works for any future development as Adventure Works is not longer updated or maintained. However, in my experience with both databases Adventure Works is more intuitive and, given its long history and familiarity, makes for a better source for various training and demo sessions.

Step 3: Database Modifications (Optional)

You might consider running UPDATE statements against the fact tables in your sample database to shift the dates closer to the current year. This will make your database and sample queries more realistic. Additionally, to support the tabular model in Step #4, even though this is a sample database you should probably create a SQL View object for each dimension and fact table to be loaded to the model.

Step 4: Analysis Services Tabular Model

With your relational sample database available (and preferably with a layer of Dim and Fact views created), you can now create a Tabular solution and deploy it to the same server from Step #1. If you’re getting started with Tabular model solutions, MS Docs provides good instructions on the creation and deployment of the Tabular project from SSDT.

Step 5: Solutions and Projects in SSMS

Steps for completing all the necessary tasks with SSMS solutions such as creating a project for a solution are well documented. Per the example, you probably want to align your projects with the groups/categories in the official function reference documentation.

At this point, you just need to add queries to your projects representing the various functions. You might block out a comment section at the top of the query to include a URL to the actual function and to document a few other essentials (syntax, return value, required vs optional parameters). If you have multiple sample Tabular models you might also leave a comment noting which one the query refers to.

The Power Query (M) queries only require Power BI Desktop (PBIX) and access to your source database. Given the volume of example queries in each PBIX, make sure you disable the load of each example query to the data model. (Per the image in the slideshow the PBIX files should be essentially empty as they only contain the queries and a preview of the data).

Brick by Brick

This is already another long post so here are a just few techniques you may consider:

1. Commit to adding just 2-3 functions to your library per day from a single category such as T-SQL Mathematical functions.
   • Identify and note the functions you’re going to review the day before.
   • Riding to and/or from work on a public transit system (rail, bus) is sometimes a good option.
   • The goal is just to be consistent such that it becomes a daily routine.
2. Switch into a separate category of functions or even a separate language if applicable if you’re getting bored.
   • Some functions such as with text manipulation are straightforward and just not as interesting as other functions (ie RANK).
3. The first example query you write each function should be as simple as possible.
   • It just needs to return correct results using the minimum parameters.
   • Based on the variety of use cases for the given function and the number of parameters you’ll create additional query examples, within the same query file (below the simple example).
     • More advanced examples may include several other functions as an input or vice versa to the given function.
4. Look to leverage a common source table or combination of source tables across many example queries. This just makes it easier to compare/contrast the functions.
5. Think of the big picture.
   • This takes time and can be frustrating at times when queries don’t run or they do but don’t return expected results.
   • Like about everything, the queries get easier the more you write and if you’re consistent you may be surprised by how much progress you make (an more importantly, how much you learn) in just a few weeks and this progress will carry over into real-world projects.