Category Archives: SSAS

Dynamic Column Level Security with Power BI and SSAS

Last week I was asked to tackle a requirement by a customer adopting Analysis Services to enable data exploration and ad hoc analysis by their users. One of their requirements was to secure columns based on a grant related to a cost center. For example, a grant has several attributes, with some attributes being considered “sensitive” and other attributes considered “non-sensitive”. Non-sensitive grant attributes would accessible to all users while a subset of the attributes in the grant table considered “sensitive” would be accessible to users related to the corresponding cost center. The challenge here is that while Analysis Services supports column level security, dynamic column level security is not supported. So my colleague and friend, the great Steve Pontello, and I put our heads together to address the requirement.

Continue reading Dynamic Column Level Security with Power BI and SSAS

Converting a Power BI Desktop File from Import to Live Query

A customer of mine is in the midst of a proof of concept using SQL Server and Power BI. During the POC, all the modeling was done in Power BI Desktop. Now that the POC is coming to the next phase, the customer is ready to move the Power BI data model to Analysis Services. But the problem is that all the visualizations in the Power BI Desktop file based on the imported data model will need to be recreated in a new Power BI Desktop file using a Live Query connection to Analysis Services. If the visualizations and reports are extensive, this could be quite a bit of work.

In this blog post, I’m going to walk you through modifying a Power BI Desktop file with an imported data model to use an external data model hosted in Azure Analysis Services or SQL Server Analysis Services 2017. This isn’t supported by any stretch of the imagination but if you’re in a pinch and have to convert a Power BI Desktop file from an imported data model to Live Query then this may be helpful to you. Also, this method works as of the January 2018 release of Power BI Desktop but there’s no guarantee that this method will work in future releases of Power BI Desktop. Continue reading Converting a Power BI Desktop File from Import to Live Query

How to Automate Processing of Azure Analysis Services Models

I’ve been working on a proof of concept for a customer that involved using Azure Analysis Services as a cache for some data in an Azure Data Warehouse instance. One of the things I’ve been working on is scheduling the automatic processing of the Azure AS database. I did find the following documentation on the process, but the screenshots of the Azure portal are out of date and I did find some errors in the instructions. I also found this very extensive project for partition management in Azure AS, but this was a little overkill for my purposes and I was just interested in the very basics.

Read my recap for MS Data Summit here

These previously mentioned resources led me to write this blog post. In this post I’m going to leverage the previously mentioned article and walk through creating an Azure Function App to automatically refresh my Azure Analysis Services model, while correcting a few errors and updating the screenshots.

If you’re new to Azure Analysis Services, take a look at this documentation. For the purposes of this post, I’m going to assume you have a basic understanding of Analysis Services.

Continue reading How to Automate Processing of Azure Analysis Services Models

Microsoft Business Intelligence Materials from Discussion at Jacksonville University

Today I had the wonderful pleasure for leading a discussion regarding the products, services, and tools Microsoft provides in the business intelligence stack. I met with Jacksonville University students and faculty and also had the pleasure of speaking with members of local businesses that are interested in leveraging Microsoft technologies. It was a wonderful experience and I had a great time!

If you’d like to download my slide deck from the discussion and presentation today, you can find that here.

Continue reading Microsoft Business Intelligence Materials from Discussion at Jacksonville University

10 SQL Server Data Warehouse Design Best Practices to Follow (Part 1 )

This past Saturday I had the pleasure of speaking at SQL Saturday #552 here in beautiful Jacksonville, Florida. My good friend, Mitch Pearson (blog | twitter) and I presented our session, Designing a Data Warehouse from the Ground Up. We had a great crowd and lots of great questions from the audience!

Watch Designing a Data Warehouse from the Ground Up Webinar Recording

With all the talk about designing a data warehouse and best practices, I thought I’d take a few moment to jot down some of my thoughts around best practices and things to consider when designing your data warehouse. Below you’ll find the first five of ten data warehouse design best practices that I believe are worth considering. This list isn’t meant to be the ten best “best practices” to follow and are in no particular order. Of course, each design scenario is different so you may find that some of the best practices listed here aren’t optimal in your specific situation.

Continue reading 10 SQL Server Data Warehouse Design Best Practices to Follow (Part 1 )

What’s New in Power BI Webinar Recording is Available

Thank you to everyone that attended my webinar with the PASS Excel BI Virtual Chapter today! I’d also like to thank Thomas LeBlanc for having me to present. I had a great time presenting on the newest features of Power BI and what features you will see in the near future. Continue reading What’s New in Power BI Webinar Recording is Available

10 DAX Calculations for your Tabular or Power Pivot Model (Part 2)

This is part two of my 10 DAX Calculations blog series. In this series I’ve blogged ten different DAX calculations you may find useful in your Power Pivot, Tabular or Power BI model. So if you missed part one, I would encourage you to check that out.

Read 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

So here’s five more DAX calculations (in no particular order) that I hope you will find useful. Enjoy!

5. DAX Use an Inactive/Custom Relationships in a Calculation

In Tabular and Power Pivot models, you are limited to only one Active relationship between two tables. Often in a relational database you’ll have a role-playing table or a table that is related to another table with more than one foreign-key. A common example of a role-playing table is a Date dimension table since our fact tables often have multiple dates, which is what the following screenshot depicts. We can see that there are two relationships between the Sales and Date tables.

image

The solid line indicates that this is an Active relationship. The dotted line indicates that relationship is Inactive. In the Manage Relationships window, you can clearly see that one of the relationships is Inactive (highlighted in red). What this means for us is that during slicing, dicing and filtering the Active relationship is used.

The good news is that we can still build calculations that leverage Inactive or unspecified relationships in our model! To do this we can use the USERELATIONSHIP function. This function allows us to specify which columns to relate two tables together.

Here’s an example of a calculation for Total Quantity that uses the relationship highlighted in red in the above screenshot:

Total Quantity by Update Date:=CALCULATE([Total Quantity],USERELATIONSHIP(Sales[UpdateDate],'Date'[Datekey]))

 

So when we slice the Total Quantity by Update Date calculation by the Date table, the relationship used is the not the default Active relationship as is the case with the Total Quantity measure. We can clearly see this illustrated below in the pivot table:

image

4. DAX Semi-Additives Measures Opening Period and Closing Period Calculations

Sometimes we have a requirement to create a semi-additive type calculation. If you’re familiar with creating SSAS multidimensional cubes, you’re probably familiar with the concept of semi-additive measure. A semi-additive measure is a measure that is not fully aggregate-able meaning that to find the correct measure amount we cannot aggregate the measure across all time. Consider a measure for account balances or product inventory. If we wish to calculate the current product inventory for the year we must return the inventory on the last day of the year instead of summing the account balances for each day in the year.

There are a couple useful DAX functions we can use to find the opening period balance/inventory or the closing period balance/inventory: FIRSTNONBLANK and LASTNONBLANK.

Here is an example of an opening period type calculation. This calculation will return the first non-blank value in a given date range. Ideally, this will be the product inventory on the first day of the month or year.

Starting Inventory:=CALCULATE([Sum of Inventory], 
FIRSTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a closing period type calculation. This calculation will return the last non-blank value in a given date range.
Closing Inventory:=CALCULATE([Sum of Inventory], 
LASTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a sample of the query results:

image

Now I can easily calculate inventory growth rates!

Inventory Movement:=[Closing Inventory]-[Starting Inventory]

 

Inventory Movement Percentage:=DIVIDE([Inventory Movement],[Starting Inventory])

Pretty cool stuff!

image

3. DAX Previous Year, Previous Month, Previous Quarter Calculations

There’s a series of specific DAX functions that I think you’ll find useful for calculating the measures for a previous period:

PREVIOUSYEAR

PREVIOUSQUARTER

PREVIOUSMONTH

In part 1 we looked briefly how to calculate the previous MTD calculation, but these functions can also just be used to calculate the previous period metrics and compare the previous period to the current period.

Here’s a couple of examples of using PREVIOUSMONTH and PREVIOUSYEAR to calculate the Starting Inventory for the previous periods:

Previous Month Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSMONTH('Date'[Date]))

 

Previous Year Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSYEAR('Date'[Date]))

 

In the query results we can now see that we’re able to calculate the previous month and previous year starting inventory amounts. Excellent!

image

2. DAX First Day of Year, Quarter or Month Inventory/Balance Calculations

The previous examples in this post have all been calculations relative to the level of the Calendar Hierarchy being browsed, but sometimes we just need to calculate the measure on the first day of the year, quarter or month no matter what level we’re browsing. For those situations, we can use the following functions:

STARTOFYEAR

STARTOFQUARTER

STARTOFMONTH

Here you can see the calculations for the Year Starting Inventory and Quarter Starting Inventory:

Year Starting Inventory:=CALCULATE([Starting Inventory],STARTOFYEAR('Date'[Date]))

 

Quarter Starting Inventory:=CALCULATE([Starting Inventory],STARTOFQUARTER('Date'[Date]))

 

And now we have the Inventory Amounts for the first day of the year or quarter:

image

1. DAX Rolling Sum and Average Calculations

This type of calculation is easier to set in a multidimensional cube in my opinion but with that said I am more of a cube guy. To get this working, I first created a calculation that sums the total Sales Amount for each day in the past 90 days.

To do this I used the following functions:

DATESBETWEEN

FIRSTDATE

LASTDATE

DATEADD

And here’s the calculation:

Rolling 90 Day Total Sales Amount:
    = CALCULATE([Total Sales Amount], 
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE( /* FIRSTDATE gets the first date in this date range which will be 90 days ago */
                DATEADD( /* DATEADD lets me navigate back in time */
                    LASTDATE('Date'[Datekey]) /* This returns the last day no matter if I'm at the day, month or year level */
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey]) /* Use the max date for the end date */
            )
        )

 

Review the comments in the code above to get a better idea of what all the functions are being used for.

Then I created a measure that counts the days that exist within the past 90 days. If the day I’m counting is the first day in my calendar that I have data, I only want to count that one day. This calculation is very similar to the previous calculation except I’m using the COUNTX function to count the rows returned.

Day Count:
    = COUNTX(
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE(
                DATEADD(
                    LASTDATE('Date'[Datekey])
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey])
        )
    ,[Total Sales Amount])

 

Now that I have a calculation to get the rolling total sales amount and count the days all I need to do is simply divide the two numbers:

Rolling 90 Day Average Sales Amount:
    =DIVIDE([Rolling 90 Day Total Sales Amount],[Day Count])

 

And here’s the query results:

image

Notice that for January the Day Count measure shows only 31. This is because prior to January there’s not data so the Rolling Average is only considering days where data exists. Also notice that the numbers for March and Q1 match since both periods should have the same amount of sales and the same number of days.

Resources

10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

Watch this Power Pivot 101 webinar recording

Feedback?

Let me know your thoughts on these calculations! There’s 100 ways to skin a cat so if you have a different or better way to perform some of these calculations I’d love to see your solution! Thanks for reading! 🙂

Data Warehouse Design Challenge: Relating a Temporal Fact Table to a Date Dimension

This past week I ran into an interesting challenge with a client. The data warehouse is capturing testing data for an educational institution. In the screenshot below, you’ll see Continue reading Data Warehouse Design Challenge: Relating a Temporal Fact Table to a Date Dimension

Data Warehouse from the Ground Up at SQL Saturday Orlando, FL on Oct. 10th

SQL Saturday #442SQL Saturday #442 is upon us and yours truly will be presenting in Orlando, Florida on October 10th alongside Mitchell Pearson (b|t). The session is scheduled at 10:35 AM and will last until 11:35 AM. I’m very excited to be presenting at SQL Saturday Orlando this year as it’ll be my first presenting this session in person and my first time speaking at SQL Saturday Orlando! If you haven’t registered yet for this event, you need to do that. This event will be top notch! Continue reading Data Warehouse from the Ground Up at SQL Saturday Orlando, FL on Oct. 10th

#MDXMonday: Finding the Current Day

This week I’m teaching the Pragmatic Works Intro to MDX virtual training class. A student in the class asked how they could find the current day sales amount using MDX (no SSAS functionality) and I thought this was a worthy blog topic. This solution assumes that the cube is processed at least once a day as the query you’re about to see returns the last day in the cube that we data for.

View previous posts in the #MDXMonday series

The first part is where the most work takes place. I created a named set to identify

Continue reading #MDXMonday: Finding the Current Day