There is not well-known feature of MDX – embedded VBA functions. If we want to perform easy excel-like operations on the cube level we can accomplish that by using set of functions which are listed here.
How to use it? By using prefix vba! Like here:
WITH MEMBER vbaTest
vba!left(“This is sample text”,8)
SELECT vbaTest on 0
FROM [Adventure Works]
And the result will be:
Transact-SQL give us possibility to automatically create aggregates based on a query. In this article I will show shortly how to use ROLLUP,CUBE and GROUPING SETS.
ROLLUP is a part of GROUP BY clause and we use it like this
SELECT a,b,c, sum(d)
GROUP BY ROLLUP(a,b,c)
ROLLUP calculate sum of d attribute of few levels: ABC,AB,A and additionally sum of everything.
Today I would like to talk a little bit about two MDX keywords: SCOPE and THIS. Below I would like to show how to use it and build something called subcube.
Subcube is a simple term when we talk about slice and dice our cube. For Example if our multidimensional cube has only one dimension attribute: Year and OrderQuantity as a measure, if we filter this dimension to have only year 2010 – it will be subcube of entire cube.
As you can see here, SCOPE syntax is very simple – in brackets we have to put a subcube expression. After this we can operate on specified subcube, if we want to change it we need to use another keywords – END SCOPE. If we close our SCOPE with END SCOPE our script comes back to default scope(entire cube).
Sometimes in my work i’ve got problems with integrity of data, especially when I need to build cube based on OLTP systems. The easiest way is to build multidimensional cubes on DW but sometimes there is no possibility to have DW. Poor situation but Analysis Services give us opportunity to deal with it.
Perfect situation is when our solution has all data that it needs – every fact table refers to existing dimension row. But what if something refers to non-existing ID of our dimension we can easily solve this problem by setting UnknownMember property. Below I will show small demo how to recognize this problem and solve it.
First of all we need to build small cube project based only on two tables from Adventure Works DW:
After build Multidimensional project we should see something like this: