All about BI, Modelling, DAX and MS BI
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...
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...
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...
Most of the times you will use SSIS or a SQL Agent job to process a SSAS model, but sometimes you don’t have either one at your disposal. Today we will look at using the regular Windows Task Scheduler and PowerShell to process a partition. The first thing you have to make sure of is that the SQL Server PowerShell commandlets are installed, more on this in this help document. In short if you have...
Dynamic security is one of those design patterns you hear mentioned every now and then by the seasoned SSAS pro’s. It is a design pattern that allows you to move the user management from the user and role management of the SSAS metadata to be part the data itself. This makes it much easier to add and remove users without having to update the cube each time, this is mostly very handy in larger implementations....
With the update of Power BI last week you can now add your own maps in any size or shape to Power BI so you can extend the set of out of the box shapes that come with Power BI. This is possible because the Shape map visual is build upon TopoJSON which is an extension of the the GEOJSON standard. This makes it super easy to add any map you want,. In this blog...
Just a quick one today as I do get this questions every now and then: How can I change which slicers interacts with a visual? By default Power BI is very interactive, anything interacts with anything but in some cases you might not want this. Excel has this build into its slicer functionality since day one but Power BI only added this in early 2016 so I can imagine some folks missed it. Let’s look...
Sometimes you want to be able to offload work for one of your Power BI Gateways as you might have many users who are building reports that need refreshing or are connecting live to the same data source, all of this is going over the same gateway machine, with the same CPU and bandwidth. It would be great if we can spread the load a bit but at the time of this writing the Enterprise...