Migrating SSAS Multidimensional Cubes to Tabular

Microsoft Azure Analysis Services is a fully managed platform as a service (PaaS) which uses the tabular semantic data model. Since the advent of the Tabular model (on-premise and subsequent Azure Analysis Services), there is an increasing number of companies aiming to build their OLAP cubes using this mode or migrate their legacy Multidimensional cubes to Tabular mode.

When it comes to migration, it can take tremendous efforts and requires in-depth knowledge of both modes to convert a complex SSAS Multidimensional to Tabular.

Let’s begin with taking a look at the aspects of both in detail to understand these difficulties and how they can be overcome. Below is a very high-level look of the two modes:

On the left is the Multidimensional model which was available from SQL Server 2005 and is made of multiple XML files. For each dimension, there is one corresponding XML file and separate XML files for cubes, roles, data source and relationships between physical tables.

On the right hand is the Tabular mode which was introduced from SQL Server 2012 and consists of a single JSON file to keep the meta data of the OLAP cube. All the objects such as Dimensions (tables), Measures, Roles, Hierarchies, Relationships and so on are hosted in a single JSON-based file.

The two modes differ in terms of features, as seen below, which is always worth considering before migrating:

After identifying these differences, we now understand how complex the task is to undertake the migration from Multidimensional to Tabular mode. The most straightforward way to achieve this is to simply manually re-build all the objects for Multidimensional mode on Tabular mode, which is not a big task for a small cube comprising of a few dimensions, but it soon becomes a nightmare when we have to rebuild hundreds of these above objects from scratch under Tabular mode. This may take months to accomplish.

Our Method
At BizOne, we have bypassed these obstacles by simply breaking things into small pieces. In this episode, we demonstrate step-by-step how to solve the relevant puzzles. Let’s look at how we did it:

The above feature is a simple look at our method to approach the difficulties. We are using XSLT (Extensible Stylesheet Language Transformations) to transform all the relevant XML files into a single out JSON file which is the backbone of a tabular mode cube.

In this episode, we will start with converting a Multidimensional data source into a JSON file which can then be added in Visual Studio under Tabular mode.

The data source meta data of a Multidimensional mode is stored in file with .ds extension. Here is the simplified mappings between both worlds:

Basically, we need to read the file .ds and extract the highlighted details and convert them to the JSON format of a Tabular mode. To do so, simply follow these steps.
1. Eliminate all the namespace in the XML file (as this approach may not work properly with an XML containing namespaces)
2. Build an XSLT file used as a stylesheet template for mapping the above details
3. Once previous steps have been completed, open the XML .ds file to add a header to interpret the XLST file.

Let’s begin!!

1. Eliminate all the namespaces in the XML file

You can do so by writing C# code which is quite straightforward, I won’t talk much about this in this series. There are several online articles discuss this topic waiting for you to discover such as here.

The .ds file should look like this after eliminating namespaces:

2. Build the XSLT file to transform necessary meta data.

Firstly, we need to add a root template to build a JSON structure in accordance with tabular mode structure, it will be like below:

I currently set the compabilityLevel to 1200 and value of annotations to 400 which means it is only compatible with SQL Server 2016 backward, including Azure Analysis Services (in a separate episode, I will explain how to configure these numbers).

Then we prepare the content of template datasource (highlighted), which looks like this:

In the above xml, pay attention to the highlighted elements:

Finally, append the datasource template to the root template to make a complete XSLT file, and save it as datasource_xslt.xsl

3. Edit the xml in the .ds file

Open the .ds file with your preferred text editor, such as Notepad or Notepad++, add the following XML tag on the header and save it in the same folder with the XLS file:

<?xml version=”1.0″?>
<?xml-stylesheet type=”text/xsl” href=”datasource_xslt.xsl” ?>

Open the .ds file with a web browser, you will see all the necessary data sources will now be converted to JSON format.

Next, open Visual Studio and create a SSAS tabular project with integrated workspace version 2016, view code of the Model.bim, then copy and paste the above result, then save. Close the Model.bim, and right click -> view designer, you should see the data source migrated as below figure.

In Part 2 of this series (coming soon), we will introduce converting Roles!

If you want to learn more about the benefits of moving to Azure, read more here.