Press "Enter" to skip to content

Category: Analysis Services

Deleting SSAS Cube Partitions With Powershell

Published 2017-09-12 by Kevin Feasel

Richie Lee shows how to remove Analysis Services cube partitions using Powershell:

One such an example of ad-hoc DBA tasks was when I had to delete about 600 partitions from a measure group that had thousands of partitions. Doing this manually would be ridiculous, so at the time I created a SQL script that used some dynamic T-SQL to create the delete commands in XMLA. XMLA has no “delete if exist” type syntax, so if I needed to run this again, this dynamic SQL output wouldn’t work. And so I decided that if I had to run the same task a gain I would write a PowerShell script that would run DSC-style and drop the partitions that were no longer required. And funnily enough, that is exactly what I had to do.

I knew I would be able to create a Powershell script that used AMO to check if a partition exists and drop it if it did. I also wanted the script to take into account any other partitions in other measure groups that may also need to be dropped. So I made sure the script uses PowerShell switches that can be included when calling the function, and if they are included then the pertaining partitions in that measure group will be deleted. So you can run the script for one, some or all of the measure groups in a cube.

Click through for the script.

Comments closed

Row-Level Security In Power BI

Published 2017-08-15 by Kevin Feasel

Paul Turley has a video showing how to use row-level security with Power BI:

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend on will depend on your data and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects.

Click through to watch the video.

Comments closed

Convert SSAS Tabular Processing Scripts Into Tables

Published 2017-08-08 by Kevin Feasel

Chris Koester shows how to take an Analysis Services Tabular processing script in TMSL format and turn it into a table using OPENJSON:

The previous post looked at how to process SSAS Tabular models with TMSL. Since SQL Server adds new JSON capabilities in 2016, let’s look at how to convert TMSL JSON to a Table with OPENJSON. OPENJSON is a new function in SQL Server 2016 that, per Microsoft:

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

In short, OPENJSON converts JSON text to a table. Since TMSL is JSON, this function can convert a SSAS Tabular processing script into a table. This could be useful if you wanted to document a SSAS processing schedule.

That’s an interesting use of OPENJSON.

Comments closed

Web Editor For Azure Analysis Services

Published 2017-08-04 by Kevin Feasel

James Serra shows off a preview of the Azure Analysis Services web designer for tabular models:

Microsoft has released a preview of the Azure Analysis Services web designer.  This is a browser-based experience that will allow developers to start creating and managing Azure Analysis Services (AAS) semantic models quickly and easily.  SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) will still be the primary tools for development, but this new designer gives you another option for creating a new model or to do things such as adding a new measure to a development or production AAS model.

A highly requested feature is that you can import a Power BI Desktop file (.pbix) into an Analysis Services database.  And once imported you can reverse engineer to Visual Studio.  Note for PBIX import only Azure SQL Database, Azure SQL Data warehouse, Oracle, and Teradata are supported at this time and Direct Query models are not yet supported for import (Microsoft will be adding new connection types for import every month).

Read on for more details.

Comments closed

Automatic Processing Of Azure Analysis Services Models

Published 2017-06-22 by Kevin Feasel

Dustin Ryan shows how to use Azure Functions to refresh Azure Analysis Services models:

Download the latest client libraries for Analysis Services. This needs to be done on your local machine so you can then copy these files to your Azure Function App.

After you’ve downloaded the client libraries, the DLLs can be found in C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies. The two files you need are:

C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Core.DLL
C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Tabular.DLL

This step is important because the documentation in Azure references the 130 assemblies, which will not work. You need the assemblies in 140 otherwise you’ll get errors.

Dustin walks through the whole process of setting up an Azure Function step by step.

Comments closed

Dimensional Design Tips

Published 2017-06-02 by Kevin Feasel

Koen Verbeeck provides some helpful hints when designing dimensions in SQL Server Analysis Services Multidimensional models:

Although traditional dimension modeling – as explained by Ralph Kimball – tries to avoid snowflaking, it might help the processing of larger dimensions. For example, suppose you have a large customer dimension with over 10 million members. One attribute is the customer country. Realistically, there should only be a bit over 200 countries, maximum. When SSAS processes the dimension, it sends SELECT DISTINCT commands to SQL Server. Such a query on top of a large dimension might take some time. However, if you would snowflake (aka normalize) the country attribute into another dimension, the SELECT DISTINCT will run much faster. Here, you need to trade-off performance against the simplicity of your design.

There are several good tips here.

Comments closed

Kerberos Constrained Delegation On Power BI Report Server

Published 2017-06-01 by Kevin Feasel

Regis Baccaro shows how to set up constrained delegation when connecting Power BI Report Server to a SQL Server instance or Analysis Services cube:

In many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.

But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server.

Read on for step-by-step instructions showing how to do this.

Comments closed

Power BI: Calculated Measures + SSAS Tabular

Published 2017-05-05 by Kevin Feasel

Shabnam Watson notes that the May updates to Power BI Desktop allow you to create new calculated measures on a report which connects live to a tabular model:

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.

If you’re interested in getting this added to Multidimensional as well, there is a request you can vote on.

Comments closed

Dynamic Filtering With Power BI + SSAS

Published 2017-04-21 by Kevin Feasel

Patrick LeBlanc shows in a video how to implement dynamic filtering with SSAS Tabular & Multidimensional in Power BI:

In this video, Patrick answers your question about how to do this in Analysis Services Tabular and Multidimensional. Also, he adds a little bit of SQL to the mix.

Make sure to watch the previous dynamic filtering videos to understand the basics of how to do this.

To begin, you need to make sure to get the URL for your published report.

I completely agree with Patrick about doing as much as you can in the source, especially if there will be more than one potential consumer aside from Analysis Services.

Comments closed