All about BI, Modelling, DAX and MS BI
This was an interesting case I did a while ago where they wanted to have a report that automatically provided a set of filters to the users, but not just any filters, no special filters tailored to them based on location and position in the organization. In this blog post we’ll look at how you can achieve this using RLS without actually securing the data. Before we start let’s look at the end result, on... Read more
One of the questions that I get asked sometimes is how to prevent users from creating groups / workspaces in Power BI. I recently found out this is actually possible and we’ll in this blog on how you can set that up. But before we continue it is good understand how workspaces in PowerBI currently work. Whenever you create a workspace it automatically creates a group in Azure AD and Office 365 with the same... Read more
So recently I created a new SSAS memory report that you can use to analyze the memory usage of your SSAS servers, read more on the actual report here. In this blog post we’ll look at how this report was build. Read more
I have created several version of the SSAS memory usage report here and here. But with the new 1200 compatibility level these memory reports stopped working due to the DMV’s not working anymore for these databases. So I decided it would be time to create a new one for all models with compatibility 1200 and up (so not for anything smaller then 1200), this time of course using Power BI. Here I can also use... Read more
As you probably have seen is that Analysis Services is now available in Azure and you can read all about in this great blog post by Bret. I have spend a few days at the SQLClinic at SQLPass talking to many of you, one of the topics that often came up is on when it would make sense to use Azure Analysis Services instead of running SSAS on your own hardware or even on VM’s... Read more
One of the questions I get regularly now that SQL Server supports RLS is how can to leverage this with SSAS. What many don’t know is that SSAS already supports this, even for Direct Query mode. So in this case you could have something like the following architecture: Dashboards and reports in Power BI that connect to an on premise SSAS server running DirectQuery mode connecting to SQL Server. All with the same username flowing from Power BI... Read more
If you have ever looked at the queries generated by DirectQuery you will see that a query that retrieves data from multiple tables gets joined by a left outer join. This might not be what you would expect but this is a actually a solid design choice. The reason is that if your data would have any keys that would be missing due to referential integrity issues on either side you would never notice and... Read more
I recently created this simple Power BI desktop file that allows you to try out dynamic security with the new security relationship feature as described in this blog post. The model contains the following tables: Sales – contains the metrics, the fact table Group – is the dimension table that groups a set of users. This can be anything in the real world, a department, a geography, productgroup, etc User – the users with their... Read more