migrate SSAS Adventure Works to icCube
Can you migrate SSAS Adventure Works to icCube? To be short: yes! and it’s relatively easy. With almost all functionality retained and opening up the many more options offered by icCube.
In this blog post I’ll show you how to migrate AdventureWorks to icCube. It’ll be a step-by-step rebuild process, so you could retrace the process yourself if you want to. For those of you who want to go to the end result straight away, you can request the Adventure Works icCube schema + data here.
Why you would consider to move away from SSAS? Well, let me ask you some questions first: When was the last time Microsoft released new functionality for SSAS? How long does it take them fix bugs? What do you need to pay if you implement SSAS over multiple services? …..
With icCube, you will get an environment that is actively maintained and developed; an analytical solution that is build for
performance, systems integration and utilization over many instances. icCube, a.k.a. the – friendly priced – and – fit for future – alternative for SSAS.
what is AdventureWorks?
AdventureWorks is the sample database that is shipped with SQL Server and which is used to demonstrate the analytical capabilities of Microsoft’s SSAS (more info here).
If you are familiar with Microsoft’s analytical engine, you might have played around with AdventureWorks before: seeing the same database in icCube will make it easy for you – SSAS expert – to have a peek at the possibilities of icCube and to evaluate the new opportunities that it could generate for your business.
If you are not familiar with this model, no worry. On the internet, loads of information is available that will help you understand the content. But even better, you can get hands-on experience on the schema itself and discover for your own in the icCube MDX engine, dashboards and/or Excel add-in. Just look for the button to get access to the schema + data in this blog post.
The version for the SSAS model that is to be migrated is “AdventureWorksDV2014Multidimensional-SE” and the cube to be migrated is “Adventure Works”. Below the model in Visual Studio (left) and the model in icCube (right):
migration success criteria
When is the migration of SSAS to icCube successful?
First of all, some background. I have found only one BI vendor so far that has tried to migrate AdventureWorks to their solution. And that is Mondrian. But when you zoom into the details you will find that AdventureWorks is used to explain Mondrian’s concepts. It just discusses how to build dimensions on top of the relational version of AdventureWorks. A completely different deal. So, I think it is fair to say that the migration of the AdventureWorks cube is uncharted area and there are no key criteria from other tools to compare with. So let’s define some common sense ones:
Criteria for a successful migration:
Any caveats need to be documented and dealt with in a satisfactory way.
Lets see how far we come with icCube.
Up to date, I could not find any record of a full migration of AdventureWorks to another BI tool
the transition process
Below you’ll find the step-by-step transition process. For this exercise, the build process in icCube has been done manually to be as complete as possible. With this knowledge at hand automated processes could very well be developed for other SSAS databases, if required, but that’s out of the scope of this blog post. (As a side note: Both SSAS and icCube have their model definition in a structured file (XML, JSON), so plenty of options for automating a migration.)
Note that the following paragraphs suggests a waterfall approach to the transition process, but in reality the migration process is more cyclical. For example, some tables views had to be defined to enable a correct implementation of a dimension in icCube or a certain relation type. I will provide details in each paragraph on these.
1 set-up of the data source
This is straight forward, as the following image illustrates:
2. defining the tables and views
SSAS uses tables and views for the definition of the Measure Groups and Dimensions. Also, expressions are used for tables to add calculated fields that are used in the dimension definition. icCube supports data manipulations (ETL) through the data transformation option and functions. But as the expressions to be migrated are plain SQL, it is more practical to define the “table source” in icCube as a SQL query.
a. table expressions
The icCube table definitions have been defined in icCube as a SELECT … FROM <table> statement, including the SQL data expressions in the “Data Source View”:
2. building the dimensions
The rebuild of the AdventureWorks dimensions has been straightforward in icCube. The dimensions in icCube contain the same hierarchies, levels, attributes, display folders and the likes.
Below an example of the Date dimension, with its hierarchies and attributes: 99.9% the same:
A few things are different in the icCube implementation. These will be discussed in this paragraph.
2a. Employee dimension (parent – child)
The implementation of the Employee dimension in SSAS is a combination of flat dimensions (attributes), a multi-level hierarchy and a parent-child hierarchy. This can not be done in the same manner in icCube.
To retain the same information (attributes, parent-child relationship and all hierarchies) two mappings have been created in icCube: a multi-level mapping (dimension “Employee info”) and a parent-child dimension (“Employees”):
2b. Treating NULL values
The NULL values for a dimension value will result in an error in icCube as it does not allow a NULL for a key value. An example for this is dimension: Employee Info – hierarchy: End Date. The source data has NULL values for employees still employed. Analysis Services has not problem with that.
icCube raises an error “key can not be Null”.
Solution: add a value for the NULL value for column End Date in the table definition:
Other columns that had to be given a NULL value are:
- Product – EndDate, StartDate, Class, ProductLine, DealerPrice, ListPrice, Size, Style, Weight, Status
- Reseller – NeverOrdered, FirstOrderYear, LastOrderYear, MinPaymentType
- Customer – YearlyIncome
- Employee – EndDate, BaseRate, SickLeaveHours
- Sales Summary Order Details- CarrierTracking
2c. Product dimension (snowflake)
The implementation of the Product dimension in SSAS is based on multiple tables: dimProduct, linking to dimProductSubcategory, linking to dimProductCategory:
To implement the same in icCube, the the three tables have been joined in a query statement in icCube’s table structure (see image). The definition of the hierarchies is
2d. discretization bucket count
The following hierarchies use the Analysis Services option called “discretization bucket count”, meaning that the dimension receive an additional level that groups the members into even sized buckets (see picture below).
As the buckets are calculated based on the setting “automatic”, the exact upper levels will be dynamic in a changing data environment (which is normal). In reality, I would argue that you would predefine the exact buckets in your model. Which could be perfectly well done using a CASE statement in the SQL statement. And that is exactly what has been done in icCube as a workaround to get the same result:
In icCube, you would set this up differently. I see two options:
- Use the option “indexing by range” in the builder and let icCube automatically bucketize numeric data into predefined buckets (see figure);
- Set-up a category function to define “on-the-fly” the bucket sizes (details here).
Other hierarchies that use the “discretization bucket count” option:
- Customer – Yearly Income
- Employee – Base Rate, Sick Leave Hours
3. dimension usage aka linking the measures
SSAS AdventureWorks recognizes the following types: Regular, Referenced, Fact and Many-to-Many. These have been implemented in icCube as follows:
|Referenced||Regular using Query that |
contains referenced information
*) Remark on linking the many-to-many dimension for Sales Reason in icCube: icCube is more strict in the bridged data. To enable icCube to load the Sales Reason flawlessly also the facts that had no Sales Reason (the NULL values) had to be included with a NULL Sales Reason in the bridge table. This has been done in the Data Tables using a query statement.
The link relationships between the fact tables and the dimensions have been copied 1:1 from SSAS to icCube:
4. defining the measures and groups
All measures and groups as defined in SSAS have also been defined in icCube. No problems found.
5. calculated members
All of the calculations (calculated measures and sets) with the exception of the SCOPEd calculations have been implemented in icCube.
Three calculations needed to be adjusted (see figure):
icCube is not familiar with the SSAS concept of KPIs. KPIs in icCube are done using a combination of the goal value as a separate measure and a visualization that will indicate if the comparison (SSAS status) is positive, neutral or negative (example in Finance demo or Waterfall visualization) . Trends are also possible to show as micro charts using vector MDX++ functionality.
Might this functionality be needed in the transition of an existing SSAS you could send an email to icCube support.
the proof of the pudding … see it in action in icCube
Are you curious to see AdventureWorks in action in icCube? Certainly, no problem at all.
This is what you need:
- a (demo) license that contains the backup functionality
- an icCube installation (running the (demo) license)
- the backup schema for AdventureWorks
benefits of icCube over SSAS
icCube resembles SSAS about 95%, but not all functionality that has been implemented in SSAS is used in icCube. Is that a bad thing? I do not think so. icCube does not have: actions (except drill-through), it does not work with KPI’s and it has avoided the potential screw ups that could be caused by the SCOPE functionality (see a little discussion about this here). All three can be perfectly solved by icCube, but in a different way, so a 1:1 migration for these is not possible.
What icCube offers over SSAS is:
- a solution that is actively maintained having a zero bug policy;
- a friendly priced BI product fit for massive deployments and integration into products;
- a complete end-to-end product; icCube includes ETL, a development environment, the server and a dashboards editor and publisher (all included);
- action-like responses in the dashboards (filtering, drill down to SQL statement, linking to external systems, …);
- endless possibilities to visualize key performance indicators, variance analysis, soll vs ist, and the like;
- lots of additional MDX++ commands (list of all commands here) ;
- matrix and vector algebra;
- inclusion of Java functions;
- inclusion of R! (read Renjin case study or blog post);
- MDX debugger;
- and much more to find on www.iccube.com.
Please share your thoughts about AdventureWorks in icCube with me. I am very curious if and how it has helped you in your quest for looking into an alternative to SSAS.
You can drop me an email at any time.