Showing posts with label SSAS. Show all posts

Logical AND and logical OR with MDX

In principle:

A set of members from the same hierarchy implies a logical OR;

A set of tupels from different hierarchies implies a logical AND.

How to make a logical AND with members from the same hierarchy

From the AdventureWorks 2012 OLAP cube, we want to find out the customers who purchased the products "Road-250 Black, 44" AND "Road Bottle Cage".

In order to do this, we need to use the EXISTS function. This function takes 3 parameters, the first 2 being sets where it looks for members from the first set that also exists within the second set. The third parameter is a measure group which provides the context for the 2 member to associate fact rows and search for the results.

SELECT
{} ON 0,
EXISTS(
EXISTS(
{[Customer].[Customer].[Customer].MEMBERS},
{[Product].[Product].&[347]},"Internet Sales"),
{[Product].[Product].&[479]},"Internet Sales") ON 1
FROM [Adventure Works]

In the above code, two nested EXISTS functions are used on the row axis. The inner function will return the set of customer members from all the members of the customer dimension who exists within the "Internet Sales" measure group for the product member key 347 which is the product "Road-250 Black, 44" from the product dimension. The resulting customer members from the inner EXISTS function will then be fed in to the outer EXISTS function. The outer function will then return the set of customer members from the fed-in members who exists within the "Internet Sales" measure group for the product member key 479 which is the product "Road Bottle Cage".

How to make a logical OR with members from different hierarchies.

From the AdventureWorks 2012 OLAP cube, we want to find the Reseller Sales Count by Product Sub Category for products who's colour is black OR who's size range is XL.

SELECT
[Measures].[Reseller Order Count] ON 0,
NON EMPTY {[Product].[Subcategory].MEMBERS} ON 1
FROM (SELECT
{([Product].[Color].&[Black],[Product].[Size Range].[All Products]),
([Product].[Color].[All Products],[Product].[Size Range].&[XL])} ON COLUMNS
FROM [Adventure Works])

In the above code the 2 hierarchies of the product dimension is brought into the same dimensionality by adding in the [All Products] member expression in both [Product].[Color] and [Product].[Size Range] hierarchies in the FROM clause. It creates a set comprising of 2 tuples, one for colour black among all the size ranges and the other for XL size among all the colours.

Members, Tupls's and Sets

A member is a dimension item. A member can be at any level within a dimension. If a dimension has user defined hierarchies, each level within those hierarchies will contain members. The member is the lowest level of reverence for a dimension item.

A tuple is the intersection of one member from each dimension/hierarchy that is participating in a query or cube context

A set is formed from multiple tuple's. In MDX you do this by enclosing all the tuple's in curly braces and separated by comma's.

The fully qualified way to reference members

[Dimension].[Hierarchy].[Level].[Member]

e.g.

[Sales Territory].[Sales Region].[Sales Region].&[Europe]

For attribute hierarchy's, the level is generally the same as the hierarchy, as attribute hierarchy's only have the one [All Members] level. In these cases many queries typically do not state the level. However it is good practice to always fully qualify the member for better readability and clarity.

Calculated Members

When creating calculated members, keep in mind that they work in relation to the dimensions used on the axis (SELECT clause). The data on the axis is restricted by either the cube context (the FROM clause) or the slicer axis (WHERE clause).

Consider the following MDX code snippet:

WITH

MEMBER [Measures].[ _Practice Patient Count] AS

([Measures].[Patient Count],[Activity Breakdown].[Description].defaultmember)

MEMBER [Measures].[Practice Expected Rate Count] AS

SUM({([ACG].[ACG Description].[ACG Description])},[Measures].[ _Practice Patient Count] * [Measures].[CCG Count Ratios])

..............

--End of calculated members

SELECT

NON EMPTY {

[Measures].CCGActivityCount,

.............. } ON COLUMNS,

NON EMPTY {(

[Location].[PCT Code].[PCT Code].ALLMEMBERS *

[Location].[Locality Code].[Locality Code].ALLMEMBERS *

[Location].[Practice Code].[Practice Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( STRTOSET('[Activity Breakdown].[Description].&[A&E General Activity]') ) ON COLUMNS

FROM ( SELECT ( STRTOSET('[Location].[Practice Code].&[P02341]') ) ON COLUMNS

FROM ( SELECT ( STRTOSET('[Location].[Locality Code].&[05J01]') ) ON COLUMNS

FROM ( SELECT ( STRTOSET('[Location].[Location].[PCT].&[05J]') ) ON COLUMNS

FROM (SELECT ClosingPeriod([Calendar].[Calendar].[Calendar]) ON COLUMNS

FROM [ACG])))))

In the above example, the data is being restricted to A&E activity on the FROM clause sub select. The query result is output at the hierarchy level by Location. On the calculated members section, the [_Practice Patient Count] member is being created as a member of the default [Measures] dimension. This member creates a patient count by A&E because the data is restricted to A&E on the FROM clause. The second calculated member [Practice Expected Rate Count] works on the [_Practice Patient Count] member by breaking the patient count by ACG's, then for each ACG count, multiplying by the CCG ratio (which was created by another calculated member not shown here), then summing the individual results up together to get the expected patient count.

Methods for communicating with SSAS

SSAS uses XMLA as the primary communication protocol which is a SOAP based XML API.
You can use the SOAP protocol with web services and communicate with SSAS purely using XMLA. Doing this ensures platform independence since it does not rely on any platform specific provider libraries. However it can be quite cumbersome and not strictly appropriate for local client applications that have no requirement to work over the internet.

For this reason, the standard driver libraries for OLEDB and ADO.net which are used for communicating with traditional relational databases has been adopted into OLEDB for OLAP and ADOMD.net respectively for communicating with Analysis Services. As with the standard libraries, OLEDB for OLAP is an unmanaged library and ADOMD.net is managed by the .Net framework. These libraries essentially have been build as XMLA wrappers. They encapsulate the XMLA protocol within them and hides all the complexity of serializing and de-serializing to and from XML. They return data the same way the standard libraries return data, i.e. the OLEDB for OLAP returns standard RowSets whilst ADOMD.net returns ResultSets and RecordSets.