Three days, more than 100 sessions, on 10 different tracks, Campus Days Denmark has become quite the act in town. I submitted three session abstracts. I was lucky and very delighted, that one of those got chosen. I will be presenting, in cooperation with my colleague Rasmus Reinholdt Nielsen (LinkedIn|Twitter), on the topic Optimizing BI with SQL Server 2014 in-memory database.
We will be showing how to optimize you BI solution through the use of the new SQL Server 2014 In-Memory database technology, called Hekaton. The first of two scenarios, will demonstrate how to performance optimize your ETL by using Hekaton as reference database. The second demo, will be building a ROLAP cube on top of Hekaton, to see what benefits can be achieved here.
See you there?
August 23rd will be the day I speak publicly for the first time. The sorry crowd will be members of the Microsoft BI Professionals Denmark User Group, in short MSBIP. See more at http://msbip.dk/ or http://www.linkedin.com/groups?gid=4069528&trk=hb_side_g
I will be speaking about how to create a custom assembly, in order to use the power of the relational engine knitted into MDX. I will be taking the information described in this blog post here, and combine it with the bloated version of AdventureWorks, thanks to Bob Duffy (blog|twitter), in order to get some critical mass to display the advantages.
At a client we had to produce a server, in order for us to have a development environment, since they couldn’t spare the server power. Odd situation, but that’s not the point. The point is that, somewhere during the installation process of this server, after installing SQL Server, we noticed that the server wasn’t a domain controller. Now the SQL Server and in particullar Reporting Services (SSRS) was installed with credentials granted to the build-in Network Services account. This account gets a new SID once we installed the domain controller, and after that SSRS started acting on us.
The new Network Services account needs to be granted modify permissions to the following folders:
- C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles
- C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RS\TempFiles
Often I meet the business requirement of listing data for the last X months. In this blog post, I will try to give my shot at, how this is done elegantly and with maximum flexibility.
We start of backwards and enhance the Data Source View in the cube with a custom Named Calculation called UtilityKey (or whatever you like). The query is probably the most simple Named Calculation you’ll ever write, since it should be looking awfully a lot like the next figure:
When this Named Calculation is ready to go, we need to make use of the fact that having a dimension with the key = 0, means we have a hold of every row in that fact table! Enter shell dimension.
We create a table in SQL Server that holds the dimension we want to be able to slice by, using the utility key; In this case: A periodicity dimension. A dimension that hold the members by which we want to slice data. The table in this example looks as follows:
We then populate with data that matches our business requirements, in this case we need the members All, 1 Month, 3 Months, 6 Months and 12 Months:
This dimension table, we then use to create a dimension in the cube, based on the key and name columns.
Ending up with a result, after trimming the names, setting IsAggregatable = False etc:
The clever part of this shell dimension is that we then define a SCOPE in the cube that allows us to slice the data as we see fit. We use the regular time dimension present in the cube to navigate through the five (5) different members of our Periodicity dimension, like this:
Slicing the cube by Periodicity and not by the regular Calendar dimension yields the desired result to the business requirement.
If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.
When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:
You will then be able to select on or more of the roles available on the database via a dialog:
This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity
The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:
Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example