Archive

Archive for October, 2012

MDX #19 – There is more to do to concatenate row values into column

October 19, 2012 Leave a comment

Continuing from MDX #18 – Easy to concatenate row values into column in MDX.

I have not accomplished the goal of concatenating the set of top resellers in each country.

Count() function can count the number of tuples in a set

In this query, I am adding a count to show how many top sellers in the set for each country. The number [Top n] is 21, which is obviously wrong. It should show 3 for each country. 21 in some sense is correct too, since there are indeed 21 tuples in the set  (3 resellers x 7 countries including an ALL country). 

clip_image001

Adding a Existing key word to forces set [Top n Resellers per Country]  to be evaluated within the current query context

clip_image002

Autoexists rule, which prevents tuples of attributes from the same overall dimension (such as attributes zip code and city in customer dimension) from existing if the dimension did not have at least one combination of them (meaning valid zip codes in a city.

Here [Country] and [Reseller] are two attributes that come from two different dimensions [Sales Territory] and [Reseller], the Autoexists rule doesn’t seem to apply.

Existing is a useful MDX operator that evaluates set_expression within the current member context (current member context sounds a little foreign, but it really means the sub-space in our Y axis which is the [Country] in dimension [Sales Territory] in the above query). If the existence of members in set [Top n Resellers per Country] depends on the current member context (in all dimensions other than just those  of the set [Top n Resellers per Country]), the returned set will reflect the context. The long verbiage means that the returned set will be only in the context of each country, which is 3.

See EXISTING Keyword (MDX) for more details.

Show all 3 tuples in each country in a big string

SetToStr() function takes a set and converts it into a big string with each member being fully qualified.

clip_image003

The big string is not really useful to our business users.

Get a list of the reseller name only

We can use the second variation of the Generate() function to strip off the qualifiers and only get the name value for each member.

clip_image004

Although the Item() function works with the above query, I am still a little fuzzy on how the nested Item() function works.

This is how the set of 3 tuples looks like for France. Each tuple has two fully qualified members

{    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[638]),
    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[175]),
    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[85])
}

I guess the first Item(0) returns each tuple, and the second item() function returns the first member France when index is 0 and returns the second member when the index is 1. Item() function works on zero-based index.

With the help of Count(), Existing, Generate() and Item() function…..

Finally we concatenated the three top resellers in each country.

SSAS #28 – Setup to Learn SSAS Stored Procedures

October 16, 2012 Leave a comment

Having worked with SSAS for a while and done some reporting with MDX queries, I started to explore opportunities to write SSAS “stored procedures”. I stumbled on a few blocks along the way to even set up the development environment properly before I got chance to identify scenarios to justify the effort. 

Learn from Expert – Analysis Services Stored Procedure Project on CodePlex

I find that the best place to start is to learn from expert. The above project has been around for quite a while, and is a wonderful place to start. But gluing all the pieces together is still tough.

Here are the notes from my exploration.

What are Assemblies on Analysis Services?

Analysis Services stored procedures are not written in MDX (as TSQL stored procedures written in TSQL). They are really “functions” that are associated with a common language runtime (CLR) or Component Object Model (COM) class. The class is in the form of a dynamic link library (DLL) , and needs to be registered as an assembly on the Analysis Services server or in an Analysis Services database.

Register the DLL file on the AS Server

Once I have the DLL file, I can go to the the AS Server, and register it as a server assembly.

clip_image001

A few things about the registration.

  • As for the Type of assembly, we have choice of either.NET Assembly or COM DLL. Since the ASSP project used C#, choose .NET Assembly. Although Analysis Services supports both COM and CLR assemblies, CLR assemblies are recommended because of the enhanced security available to CLR assemblies. See Using Stored Procedures (MDX).
  • Assembly Name: ASSP is the namespace of the ASSP project, and all the functions will need to be prefixed with ASSP for execution.
  • Permissions: I am not exactly certain what each level of permissions entail, but certain functions in the ASSP project need “Unrestricted” permission.
  • Impersonation should be left as Default.

clip_image002

Referencing ADOMD.NET in a C# Class Library Project

Creating a Class Library project in C# is straightforward, except the References setting.

  • ADOMD.NET has exposed both the Analysis Services server and client components through ADOMD.NET.
  • Microsoft.AnalysisServices.AdomdClient: this is the namespace in ADOMD.NET that allows all client side programming against the AS. The file Microsoft.AnalysisServices.AdomdClient.dll was found under C:\Program Files\Microsoft.NET\ADOMD.NET\90.
  • Microsoft.AnalysisServices.AdomdServer: this is the namespace in ADOMD.NET that allows all server side programming against the AS. The DLL file name msmgdsrv.dll is found on my PC under C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\.

clip_image003

Versions of Target .NET Framework

Both the client and server references of ADOMD.NET  on my PC are version 9. After a few trial and error, I found that I can set the Target .NET Framework to 2.0, 3.0 or 3.5 (except 4.0), and the DLL worked for the AS 2008 R2.

clip_image004

Run the first ASSP Like()

The Like() stored procedure is simple enough.

clip_image005

So much work….

but I still haven’t found a scenario where using an Analysis Services stored procedure is justified….but I am pretty sure it’s worth the effort to set up a development environment, just in case….

Categories: SSAS Tags: , ,

MDX #18 – Easy to concatenate row values into column in MDX

October 7, 2012 Leave a comment

Concatenating row values (within groups) into column in SSRS and TSQL

Concatenating row values into column is a never ending topic for report developers. It should be a simple task. That is, if the rows are not within groups. If the rows you are about to concatenate are customers in each state (VS. all the customers), then the topic became a special case of aggregation (as opposed to SUM, AVG etc.).

We can achieve this in SSRS and TSQL, with relatively very little effort.

Easy to concatenate row values (not in groups) into column in MDX

This query retrieves the top 2 Countries in All Region. It does a few things:

  • Creates a named set, [Top n Country].
  • The named set contains top 2 countries in all sales territory. They are United States and Canada.

clip_image001

What if our business users would like to show the top 2 countries in one row?

One quick way is to use the SetToStr() function

The SETTOSTR() function does what its name says, concatenating all members in the set into one big string.

clip_image002

A few of notes about the SETTOSTR() function:

  • Here is the syntax: SetToStr(Set_Expression)
  • It is primarily used to create a string-representation of a set that can be parsed by an another function. My example does not do any string parsing. VBA functions are routinely used for string parsing/manipulation in MDX queries (or calculations). Not all VBA functions are supported in MDX. Here is the reference for VBA functions in MDX and DAX. Replace() and many other VBA functions are not supported in MDX, which is a disappointment. But many other nice functions, such as IIF, LEFT, MID etc are supported in MDX.
  • The string that is returned is enclosed in braces {}, with each item in the set separated by a comma.

There are two members, United States and Canada, in the named set [Top n Country]. They both are fully qualified with the dimension [Sales Territory] and the attribute hierarchy [Sales Territory Country].

I am pretty sure that business users will freak out if they see a report like this. What’s with all the crazy brackets, ampersands, etc?

It’s a very reasonable request to see just United States and Canada, separated by a comma.

Use the wonderful GENERATE() function in the place of  SETTOSTR() function

We can use the wonderful Generate function to

  1. loop through the [Top n Country] set,
  2. for each tuple, use the name() function or member_caption function to get just the caption of the member, instead of the fully qualified member reference.

clip_image003

A few notes about this query:

  • I am using the second variation of the Generate() function: String expression syntax

Generate( Set_Expression , String_Expression , Delimiter )

  • See the MDX reference article for the details on how to use the Generate function, especially the second variation: http://technet.microsoft.com/en-us/library/ms145526.aspx
  • Here is a blog that shows an example of using the first Set expression syntax of the Generate() function: MDX #10 – Top 5 Resellers in Each Country
  • It does not take long for us to get familiar with the CurrentMember function, which returns the current member along a specified hierarchy during iteration. Here is the syntax: Hierarchy_Expression.CurrentMember.
  • But it might take some us longer to get to know the Current function. The syntax is: Set_Expression.Current. Instead of a member, the Current function returns the current tuple from a set during iteration. In our example, the first tuple is {[Sales Territory].[Sales Territory Country].&[United States]}, and the second tuple is {[Sales Territory].[Sales Territory Country].&[Canada]}. With the Name function, we strip off the qualifiers and retain only the caption.
Categories: MDX Challenges Tags:
%d bloggers like this: