Recently I was working on a project where I was building a Tabular cube that needed to have information in it that was in an already existing cube. My first thought was to just build the new cube and then add the already existing fact table into my new cube. It seems like an easy enough solution, but is it really the way to go?
After some digging around the web, I found that you can create tables from other cubes. I thought this was a very interesting concept, but there wasn’t much documentation I could find on how to do this. I thought I would share what I discovered while working with this concept.
For this blog post I made a new solution in Visual Studio 2019 that contains two SQL Server Analysis Services Tabular cubes. I have a sales cube (DemoOlap) and a forecast cube (DemoMdxTableOlap). With Power BI, I can only connect to one Analysis Service at a time. This is fine if I want to look at just sales information or just forecast information. In this situation, I want to look at a comparison of the two data sets. This does not work, though, if I want to compare the data side by side in the same report.
Let’s take a look at my sales cube schema. As you can see, it simply has a sales fact, a product dimension, an employee dimension, and a date dimension.
Now let’s take a look at the forecast cube. There is simply a forecast fact, an employee dimension and a date dimension.
As you can see, these cubes have a different level of granularity. Luckily, the forecast cube is where we want to do the sales and forecast comparison. It is also where there is less granularity so it should be pretty easy to pull in another fact for this.
We have a few options here. We could just import the already existing sales fact from the database. However, we don’t want to pull in all that information into our forecast cube. We once again also have the granularity problem to consider when doing this. Either way we will have to import a new table to create a new fact table for our reports.
To do this we will need to import from a data source. Instead of using the existing data source, we are going to create a new one.
Normally when adding a new data source you would run the Import From Data Source wizard and select Microsoft SQL Server or another database to import tables from. However, if you scroll down that list, you will see there are several other data sources you can choose from.
We will select “SQL Server Analysis Services Database” as shown in the image below.
This is the important part that makes this work. In the next screen, type your server name. Normally the Database is optional to enter, but in this case, we have to enter in the name of the database. This means that the cube you want to create a table from has to be published on an accessible server.
Below the box where you enter the database name, there is a section where you can enter a MDX query. You expose this box by clicking the side arrow next to the words MDX or DAX query (optional). This gives us a large text box to enter a query into.
If you are savvy in MDX, you can write a query to pull in the information you want. Alternatively you can open up Microsoft SQL Server Management Studio and connect to the Analysis Services instance that is hosting the OLAP cube you are going to be querying.
In object explorer, find the cube you are going to query. You can then right click on it and select Browse….
This opens a new window where you can explore the cube.
Here you can simply drag and drop fields from the cube onto the designer area and execute the query to make sure you are getting the data you want.
Looking back at my forecast cube, the fields I want are the OrderDate, EmployeeKey, and the Sales Amount. In the browser we cannot see the EmployeeKey, as it is a hidden field. Luckily for us, just because it is hidden does not mean we can’t query it. In order to query the hidden field, we need to switch out of design mode. You can do this by clicking on the “Design Mode” icon in the image below. Before doing so, to make life a little easier, I am going to bring over the email address from the employee dimension.
Once we are out of design mode, we find that there is now a text box with MDX code in it. As you will notice, by bringing over the email address from the employee field, all the necessary joins are there in the MDX to join to the Employee table.
I don’t want the email address though. I want the EmployeeKey. We can get this by replacing [Employee].[Email].[Email].ALLMEMBERS with [Employee].[ EmployeeKey].[ EmployeeKey].ALLMEMBERS. If we then execute the query to check and make sure our query works, we get the results we want.
Now we want to copy that query and paste it back in our box in Visual Studio.
Let’s click OK. Now we are presented with a screen to access the Analysis Services cube we just created the query for.
Enter your credentials and click Connect.
You will then be presented with a screen showing a truncated result set of the data we just pulled in from our cube. Click OK to continue.
Next the Power Query Editor will open.
Click Import. There will now be a new Table in your model.
All that is left to do is clean up the naming and add in some relationships so that the data will join properly and have good naming in our reports. Once that is completed and we deploy our cube again, we now see two facts with measures on them. Now if we create a Stacked Area chart, we can see our data pulls in and relates properly.
After I published, there were a few things I was curious of and explored a bit further. After processing, I cleared out the data from the sales cube that I performed the MDX query on to see if the query was doing a live query every time I queried against the Forecast cube. If it was, that could be a substantial impact on the cubes and server. When I browsed my Forecast cube, the data was still pulling through. It seems the query only runs when the cube is being processed which means there should be very little impact on the server.
The other interesting find is the fact table for my sales cube has 60,855 rows. Since my query returns aggregated data based on the level of granularity I built the query at, my sales table in my Forecast cube only has 468 rows. This means less memory will be used as opposed to pulling in the full sales fact from my Sales cube. It also means that there is no need to build yet another fact table in your data warehouse for feeding data into your cube.
Overall, this seems like a great feature that should have some interesting uses. As with any technology and feature, it has its place to be used. Using this to piggy back on multiple cubes could cause some dependency issues if other cubes fail to process, or are deprecated over time.
I also wanted to note, this can also be done in Visual Studio 2017. The screens are a bit different in Visual Studio 2017, but the concept is the same.
Please contact us to explore how SDP can leverage this technology to create a competitive edge for your business.
Kevin Williams is a Senior Software Engineer and Power BI Practice Lead at Software Design Partners. He has a Microsoft Certified Solutions Associate: Business Intelligence Design certificate and focuses in business intelligence and .NET development.
