Speaking at PASS Summit 2017

My session got selected for this year’s PASS Summit. I am beyond excited and happy! Thank you everyone in #SQLFamily for your support.

PASS_17_SpeakingSmall_250x250-AHere is my session:

Build a performance monitoring toolset for SSAS using PowerBI

Have you ever wanted to monitor the performance of a SQL Server Analysis Services (SSAS) server but did not want to create a tool from scratch and your company did not allow you to purchase one of the few commercial tools available?

Join me in this session as I show you how to use Windows Perfmon, SQL Server Extended Events, SSAS Tabular and Power BI Desktop to build a performance monitoring tool for a SSAS server.

We will start by reviewing some of the SSAS performance metrics you can collect. Then we will learn how to use Windows Perfmon and SQL Server Extended Events to collect performance counters and query execution information from a server and store it in a SQL Server database. Next we will use SSAS to build a Tabular model from the information collected. Finally, we will use Power BI Desktop to present this information.

Calculated measures in Power BI Desktop reports connected live to SSAS Tabular.

May release of Power BI  Desktop adds the capability of adding new calculated measures to a report when you are connected live to SSAS tabular. This is great because before this release, once you connected live to SSAS you could not make any changes to the model. Official announcement

CalculatedMeasurePBIDLiveConnectTabularSSAS

Ideally the SSAS database has all the measures you need but now you have the capability to add new ones if you need to.

You can control the folder (table/measure group) under which the new measure shows up by using the “Home Table” option from the Modeling tab. I really like this feature as you can create copies of the same calculation and send them to different folders for ease of use.

Other SSAS related features

There is a request open for the same feature to be added for SSAS Multidimensional. Vote here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19140997-calculated-measures-when-live-connected-to-mdx-cub

There is another request for adding calculated columns to a report connect to SSAS Tabular live. I recently ran into a situation where I wanted to create a custom column for sorting since it was not provided in the SSAS database and I did not have permission to modify the SSAS database.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19140907-create-calculated-columns-when-using-ssas-live-con

 

 

 

 

 

Clear Cache XMLA for SSAS Multidimensional

I often use the following XMLA code to clear database/cube/measure group cache in SSAS Multidimensional when I do performance testing between different MDX queries or versions of the same database. I am posting it here so that I can find it easily in future. You can use it to clear the cache at the database, cube or measure group levels.

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

YourDBName

YourCubeName 

YourMeasureGroupName

</ClearCache>

If you need to clear the cache at Database level you don’t need the lines for Cube and Measure Group. Similarly if you are clearing the cache for the whole cube you don’t need the line for the measure group.

 

 

 

 

How to change the font size of the formula bar and measures grid in SSDT for SSAS Tabular

When you create a new measures in SQL Server Data Tools (SSDT) for SSAS Tabular, you edit the formula in the top section of the user interface in a section called “formula bar”. The default font size for this formula bar is 8.

formula_bar_font_size_8_default

The small font size may be difficult to read for some people. The easiest and fastest way to change the font size is to click somewhere in the formula bar and use your mouse middle wheel while holding down the Control button on your keyboard. However, the font size will go back to the default size next time you open SSDT.

If you wish to change the font size permanently, you can use the Tools menu. Click on Tools –> Options –> Fonts and Colors –> Environment and choose “Business Intelligence Designers” from the drop down menu. Here you can change the font size. Your changes will not take effect until you restart SSDT.

tools_options_environment_fonts_and_colors

The following pictures show my environment after I changed the formula bar’s font size to 10 and 14.

Font Size 10formula_bar_font_size_10

Font Size 14

formula_bar_font_size_14

Notice how the font size change affects the table results grid and the measures grid below as well. Also if you right click on any table name tab, the font size for the pop up menu has changed.

formula_bar_font_size_10_tables_right_click_menu

Font size 10 was the easiest to work with for me. It made the formula bar easier to read while it kept the rest of the environment at a good manageable size.

MDX script keyword colors are back in SQL Server Data Tools (SSDT) release version for Visual Studio 2015

The MDX keywords in a cube script were not properly changing colors in the SQL Serve Data Tools (SSDT) Preview version for Visual Studio 2015. (14.0.60316.0). (See my previous post on this problem here.)

The different keyword colors make it easier to write, organize and read an MDX script inside a cube.

The following picture show Adventure Works script inside the latest SSDT.

SSDT_2015_GA

Here is the version information for the latest version of SSDT available at the time of writing this post.

SSDT_2015_GA_VersionInformation