Adding an Attribute or Measure to the Power BI Model

Making Changes to Azure Analysis Services Tabular Model

This document describes how to accomplish these tasks using Visual Studio & SSMS:

• Adding a Measure

• Adding an Attribute

• Deploying the Model

Downloading the Production Model

1. (If this is your first time) Get the Microsoft Analysis Services Project extension for Visual Studio 2017 or 2019

2. (If this is your first time) Get the GitHub extension for Visual Studio 2017 or 2019

3. Open Visual Studio

4. Clone/pull the current model from GitHub (in Visual Studio)

1.  Log into TLGitHubAdmin account using the credentials in Keeper

2. Clone PowerBI-Model from GitHub (if this is the first time) or open your project and click team explorer-->sync-->pull (if you just need to update to the latest version of the model)

5. Click Solution Explorer tab
6. Double click PowerBI-Model.sln
7. Double Click Model.bim

Adding a Measure

1. Find the table where you want the measure to live. The table names are listed on the tabs along the bottom. Click the ellipses on the far right to see the rest of the tables that cannot fit horizontally.

2. There is a grid in the bottom section of the table view where all of the measures are defined. Click an empty cell and type the measure name and expression at the top to create a new measure. Remember to use the := operator between the measure name and the DAX expression. 

3. After adding the measure, fill in any relevant display properties by clicking on the measure and using the Properties pane on the right. We should almost always fill in at least these three:

1. Display Folder

2. Format

3. Description

4. Save the Model.bim file when done

Adding an Attribute

1. Find the table where the new attribute needs to be added. The table names are listed on the tabs along the bottom. Click the ellipses on the far right to see the rest of the tables that cannot fit horizontally.

2. From the Extensions menu, choose “Table” and then “Table Properties”. This will open the M-language script that defines any changes that are made between SQL and the model. Click “Design” to see the GUI version of the script, which is much easier to use. Since we do all of the heavy lifting in SQL, we should only use these two functions:

1. Choosing which columns to select

2. Renaming columns (e.g. “CandidateName” => “Candidate Name”)

3. If prompted with a “Please specify how to connect” message, choose “Edit Credentials” and then enter the credentials for the service account, TL-AZ-BI-Sync

4. Add the new column by clicking the “Removed Other Columns” step, then choosing the name of the new column

5. Rename the column to a “nice” name if necessary by clicking the “Renamed Columns” step, then double-clicking the new column header and renaming it

6. Note that the “Removed Other Columns” and “Renamed Columns” steps could be in a different order, have different names, or not exist. There could also be extra steps, but our best practice is to use these two only.

7. After adding the new column, closeout by choosing “Home” and then “Close & Update”

8. When the query editor closes, choose the new column and edit any relevant display properties from the “Properties” pane. At the very least, add a display folder and format any numbers or dates.

9. Save the Model.bim file when done

Deploying the Model

1. Set the server name, database name, and processing option by right-clicking the project name and choosing properties

1. The main TalentLaunch Azure AS server is asazure://aspaaseastus2.asazure.windows.net/tlpowerbi

2. Generally recommend “None” as the processing option, which will deploy metadata only

2. Build the project (right-click the project and click Build)

1. This step is important, otherwise you will end up deploying the most recently built version of the project, which could be old

3. Check to make sure the model is not in the middle of processing

1. This logic app processes the model at the end of each job run: https://portal.azure.com/#@mytalentlaunch.com/resource/subscriptions/2f23d6dd-8045-418a-8e09-ee624549fd26/resourceGroups/PowerBI/providers/Microsoft.Logic/workflows/TL-AZ-PRD-MODELREFRESH/logicApp . If the status is “Running”, do not deploy yet. The full model process usually takes around 8 minutes, so wait until it completes.

4. Deploy .asdatabase file using the deployment wizard

1. Open the Deployment Wizard – this comes bundled with SQL Server Management Studio, it’s easiest to find by searching from the start menu

2. Navigate to the “.asdatabase” file for the model that you just built. It is in the “bin” folder of the Visual Studio project directory

3. If the file does not exist, you probably did not build the project

4. Set the server and database name if you did not already in the project properties

5. Log in if prompted (active directory authentication, use your email address)

6. Deployment usually only takes a few seconds if you choose “None” as the processing option

5. Refresh database credentials

1. Connect to the analysis services server in SSMS. Again, use your email address to login:

2. Right-click the database connection under “Connections”. This is the connection between the tabular model and the SQL database:

3. If you have the “Refresh Credentials” option, choose that. If not, choose “Properties” then click the ellipses to the far right of “Credentials”, then “Edit”. We use SQL authentication, so choose “Database” from the options on the left, then enter the credentials for the service account (TL-AZ-BI-Sync).

6. Process tables if necessary

1. “Processing” means getting updated data from the SQL source

2. If you only modified metadata (e.g. added a measure, formatted a measure, added a description to a measure or field, etc.), it is not necessary to process any tables

3. For any other changes (adding an attribute, adding a table, adding a relationship, etc.), it is necessary to process the affected tables.

1. Open the “Tables” folder, right-click any of the tables that need processing, and choose “Process Table”

2. From there, you can select multiple tables to process at once if necessary

3. Always choose “Process Full” as the mode