m-kovalsky / modelautobuild Goto Github PK

A framework for dynamically creating a tabular model based on an Excel template.

License: MIT License

C# 100.00%

tabular dax analysis-services-tabular analysis-services powerbi tabulareditor

modelautobuild's Introduction

Model Auto Build

Model Auto Build is a framework that dynamically creates a tabular model based on an Excel template. This framework is compatible for all destinations of tabular models - SQL Server Analysis Services, Azure Analysis Services and Power BI Premium (using the XMLA R/W endpoint). This framework is also viable for both Import and Direct Query models.

Purpose

To provide a framework for business stakeholders and developers when initially outlining a model. When completed, the Excel template serves as a blueprint for the tabular model - akin to a blueprint for designing a building.

This framework speeds up the development time of the model once the blueprint has been laid out. Development time can be spent on more advanced tasks such as solving DAX challenges or complex business logic requirements.

Lastly, many people who are new to Power BI are more familiar with Excel. Since the framework is based in Excel it provides a familiar environment for such folks.

Instructions

1.) Download the following files from the ModelAutoBuild folder and save them to a single folder on your computer.

  ModelAutoBuild.xlsx
  ModelAutoBuild.cs
  ModelAutoBuild_Example.xlsx (this file shows an example of a properly completed ModelAutoBuild.xlsx file)

2.) Open the ModelAutoBuild.xlsx file.

3.) Populate the columns in each of the tabs, following the instructions within the notes shown on the header rows. Close the Excel file when finished.

4.) Open Tabular Editor and create a new model (File -> New Model).

5.) Paste the ModelAutoBuild.cs into the Advanced Scripting window within Tabular Editor.

6.) Update the fileName parameter (on the 7th line of code) to be the location and file name of your saved ModelAutoBuild.xlsx file (see the example below).

string fileName = @"C:\Desktop\ModelAutoBuild";

7.) Click the 'Play' button (or press F5).

After completing Step 7, your model has been created within Tabular Editor.

If you want to deploy the model to SQL Server Analysis Services or Azure Analysis Services, view Tabular Editor's Command Line Options.

If you want to deploy the model to Power BI Premium, view the instructions on this post.

Additional Notes

It is not necessary to fill in all the details of the model. For example, the Expression (DAX) and other such elements may be created afterwards. The goal of this framework is not to create a completed model per say but to quickly and intelligently build the foundation.
If you want a column to be a calculated column, simply add in the DAX in the Expression column. Columns that have DAX expressions will automatically become calculated columns. If there is no expression they will default to a data column (where you must enter a source column). Note of caution: try your best to avoid calculated columns. If in doubt, view Best Practice #6 within this post: https://www.elegantbi.com/post/top10bestpractices.
The partition queries generated by this framework are in the following format (example below is of a fact table). This is a best practice and ensures no logic is housed within the partition query.

SELECT * FROM [SchemaName].[FACT_TableName]

Requirements

Tabular Editor version 2.13.0 or higher

Version History

2021-05-27 Version 1.4.2 released
2021-04-30 Version 1.4.1 released
2021-04-14 Version 1.4.0 released (complete code overhaul; simplified script to be executed in Tabular Editor and pull directly from Excel)
2020-07-06 Version 1.3.0 released (added support for Hierarchies)
2020-06-24 Version 1.2.0 released (added support for Calculated Columns)
2020-06-16 Version 1.1.0 released (added Roles and Row Level Security)
2020-06-11 Version 1.0.0 released on GitHub.com

modelautobuild's People

Contributors

Stargazers

Watchers

Forkers

gustavoleo ievsantillan davidmsalm robertosljunior bartekgraczyk debashish10 nexus150 data-analisis rafaelxkr bubdm padampadampadam mz0in richardsonjf somedaygone nickcodesdotnet aegonsql arneh61

modelautobuild's Issues

Can v1.4 be run from command line

I have not made the jump to v1.4. Waiting for time to test.

Can you still run the process from the command line without pasting the script in Tabular Editor?

I currently use a Powershell script to automate the build and deploy process.

Missing Decimal FormatString for Measures

It is present for columns but not for measures.

BTW The way the code is written makes this project so extensible. If it fits my client's needs I could add more types like Decimal_OnePlace and Decimal_TwoPlace depending on how many decimal places they wanted to see by default.

Calculated Column - Not removing quotes from the DAX Expression

If a calculated column is such that the DAX code generates a quoted string it will not be removed by the ModelAutoBuild.cs file.
The ModelAutoBuildExample.xlsx works because the one calculated column is a simple SUM without anything that would generate a quoted string. But as soon as I create a Calculated Column such as a PATH(id, parent_Id) or a CALCULATE as soon as the comma is added it generates a quoted string.

The current code only removes the quotes from the measures.

One workaround I am using is to remove the quotes in the same else block that creates the Calculated Column. Or else I would need to do it similar to the Measure code and iterate through each Calculated Column.
Note: I know the best method is to not create calculated columns and push that down to the database layer

Model Permission

The C# script does not support the Model Permission of Refresh and ReadRefresh. Unless this is to enforce best practices it would be helpful to have these permissions.

As a workaround, I have added it manually to the script file.

FormatDAX Deprecated in Tabular Editor Version 2.13.0

With the new version of Tabular Editor 2.13.0 the FormatDax method has been deprecated.
https://docs.tabulareditor.com/FormatDax.html
This is in an effort to reduce the workload on the Dax Formatter site.
The proposed solution is to use alternative syntax such as FormatDax(Model.AllMeasures); so that it is done in a single request.

When enabling DAX Formatting in the Model Auto Build with the new Tabular Editor it does generate this message:

Script warning: This script is making multiple calls to the "FormatDax" method, which has been deprecated! Calls will be throttled to not overload the DaxFormatter.com service. To avoid throttling, please change your script to use the FormatDax extension method going forward. For more information, see: https://docs.tabulareditor.com/FormatDax.html

Calculation Groups

Do you have plans to extend this to include Calculation Groups? Would love to see this feature.

Auto-Generate Relationship fails if similar named dimension table

There currently was an existing Dimension named ServiceRouteMaster related to ServiceRouteMasterId in the current model.

For testing purposes I created a test column in the Fact Table named TestServiceRouteMasterId and another dimension table with the name of TestServiceRouteMaster. When I went to build this I got error messages about ambiguous paths. After creating some debug outputs I was able to determine that for the new dimension table it was getting the correct column from the fact table TransactionLines.TestServiceRouteMasterId but for the dimension it was using ServiceRouteMaster.ServiceRouteMasterId.

I determined this was due to it matching the same EndsWith pattern since it read the original ServiceRouteMaster column first.

var dim = Model.Tables.FirstOrDefault(t => factColumn.Name.EndsWith(t.Name + keySuffix));

If I switch it to use Equals(t.Name + keySuffix)); it builds the correct relationship.

Could see this happening if someone had Location and CustLocation or StoreLocation.