MDX for Generating a List from Member Names – Norsk SSUG
Tusen takk til Norsk SQL Server User Group! Min kone og jeg hadde en fantastisk tid på møtet! Vi er tilbake til Minnesota neste uke, men vi håper vi skal komme tilbake til Norge snart.
Here is the solution to the MDX problem that we worked on at the meeting. It was a great problem. Thanks to the group member who brought it in and to everybody who helped work on it.
The following script has the solution in one query and a second query that displays the data in a way that shows that the solution has worked. We ran this at the meeting with Adventure Works for 2012 Multidimensional. The same query could also be used with Adventure Works for 2008.
An explanation of the query follows the code.
with member measures.test
as
generate
(
extract
(
nonempty
(
[Date].[Calendar].currentmember
*
[Product].[Category].[Category].members
*
[Measures].[Internet Sales Amount]
)
,[Product].[Category]
)
,[Product].[Category].currentmember.name, “,”
)
select {[Measures].[Internet Sales Amount], measures.test} on 0,
[Date].[Calendar].[Calendar Year].members
on 1
from [adventure works]
go
select {[Measures].[Internet Sales Amount]} on 0,
non empty
[Date].[Calendar].[Calendar Year].members
*
[Product].[Category].[Category].members
on 1
from [adventure works]
Here is a description of the problem we were trying to solve:
1. A cube has free text descriptions. These descriptions exist in the cube as the name property of one of the attributes.
2. For any subset of the data, the MDX must concatenate all the individual text descriptions into a string.
We worked on the problem using Adventure Works, using the Category attribute hierarchy from the Product dimension in place of the free text description.
It is easiest to understand the calculation when looking at the innermost function and working outwards:
1. First the nonempty function applied to the crossjoin of three sets:
nonempty
(
[Date].[Calendar].currentmember
*
[Product].[Category].[Category].members
*
[Measures].[Internet Sales Amount]
)
In our query we are putting calendar years on rows. So we crossjoin the current year with all the values from category and with the Internet Sales Amount measure. If there isn’t a sale for one of the categories for that year, it will be empty, and will be eliminated by the nonempty function. What will be returned by the nonempty function is a set of tuples – all of them will have the year, one of the products, and the Internet Sales Amount measure.
2. But we are not interested in a set of tuples, we are only interested in the values for Product Category. So we have to extract the values for that particular hierarchy from our tuples. We do that with the Extract function:
extract
(
nonempty
(
[Date].[Calendar].currentmember
*
[Product].[Category].[Category].members
*
[Measures].[Internet Sales Amount]
)
,[Product].[Category]
)
Extract takes two parameters – a set of tuples and the name of one of the hierarchies used in those tuples. We created the set of tuples with the crossjoins (the set multiplication). We filtered those tuples with nonempty. Now we’re getting the individual members from the Product.Category hierarchy that are included in those tuples. For each row this will be the set of categories that had sales in that particular year.
3. But we still have a set of members, and what we really want to see is a list of string values concatenated together. We do that with the generate function. Generate is an over-loaded function. It has two meanings. In one meaning (probably the most commonly used) generate is used with two sets to create a third set, with the membership of the second set being created using each member of the first set. In the second meaning, generate is used to create a string. That’s the form of generate that we are using here.
This form of generate has three parameters – the set, in our case a set of categories, a string that is used as the source for the output string, and a delimiter to be used for separating each of the string values. The source string for this example is the name of the current member of Product.Category. The delimiter is a comma.
The CurrentMember function is usually used in a calculation to see where we are in a cellset. That’s the meaning of CurrentMember in our calculation where we use it with Date.Calendar. In each row there will be a different year, which will be a different CurrentMember.
The Generate function creates a loop. We add another part to the string with each loop. The CurrentMember used in the string is the CurrentMember for each Category in the set as we go through the loop. So, if we have three products, each of those products will take their turn as the current member and will have their member name added to the string.
generate
(
extract
(
nonempty
(
[Date].[Calendar].currentmember
*
[Product].[Category].[Category].members
*
[Measures].[Internet Sales Amount]
)
,[Product].[Category]
)
,[Product].[Category].currentmember.name, “,”
)
The value displayed by the calculation would look like this for a year when these three categories had sales:
Accessories,Bikes,Clothing
Takk igjen! Neste gang kanskje jeg kan snakke norsk. Kanskje ikke. Lykke til med MDX! Vi sees!
Advanced Analysis Services Seminar Downloads
Here are some files for downloading for participants of the Advanced Analysis Services Seminars in Stockholm, Göteborg, København, Oslo, and Bergen in November 2011. If you’re not taking the seminars, you’re welcome to the files also – but they don’t have a lot of explanation. (Sorry!) But you can still sign up for the seminars. See the links on each of the cities above.
BI Migration from AS2008 to Analysis Services in SQL Server 2012
Microsoft Business Intelligence is entering into a very significant period of change with the coming release of SQL Server 2012. The addition of the tabular model, the new Power View tool in Reporting Services, the integration of individual BI and corporate BI, the columnstore indexes in the relational model – there are a whole new set of tools available for bringing key information to business users.
But what about those who are already using cubes in Analysis Services? What happens to all the time and effort that has been invested creating cubes in AS2008? There are some choices to be made. I have written a whitepaper outlining the four main choices, as I see them:
- Stay with cubes in the multidimensional model
- Move existing cubes to the tabular model
- Keep existing cubes, but do all new development in the tabular model
- Do future development using both models
BI Strategies Migrating from Analysis Services 2008 to Analysis Services 2012
Norsk SQL Server Users Group – 15 November 2011
Jeg har vært invitert til å tale ved Norsk SQL Server Users Group mens jeg og min kone er i Oslo. Møtet blir tirsdag 15. november kl 16:30. Her er kunngjøringen.
Her er beskrivelsen:
“Much of the power and flexibility of Analysis Services comes in creating business logic with MDX. We will discuss how to create calculations and optimize them. If you have some examples of MDX that you can share with the group, we will examine them and discuss ways to improve their performance. Also, we will take a small look at the new calculation language in PowerPivot and the next version of Analysis Services – DAX.”
Jeg ser frem til å møte dere!
Peter Myers and Tim Peterson Analysis Services Seminars in Norway
Last week I wrote about the seminars Itzik Ben-Gan and I are presenting in Sweden and Denmark. In Norway, Peter Myers, another one of my SolidQ colleagues, and I are presenting one-day seminars on Analysis Services. Here’s the schedule:
Peter Myers, Denali BI News, 10 November in Oslo
Tim Peterson, Advanced Analysis Services for SQL Server 2008 R2 and Denali, 16 November in Oslo and 18 November in Bergen
These seminars are being held by Glasspaper. Here is where you can sign up.
We hope people will consider attending both of our seminars. Peter will cover much more information about the next version of SQL Server, code-named Denali. Though I will be talking about Analysis Services in Denali also, my seminar is focused on how to optimize Analysis Services whether you are using the SQL Server 2008 version or you are intending to move to the new Denali version.
I have been working with Analysis Services since it was first releases as OLAP Services in SQL Server 7. (I actually was already working on a Data Warehousing Project with SQL Server 6 – but that was very different!) It’s a powerful tool – in many ways easy to use, but there are many things that can be done to make it work more effectively. And now with a new version, there are many more things to talk about!
Peter has taught in Norway before. I have visited Norway three times, but I have never taught a course there. And this will be my first time visiting Bergen. I am looking forward to it!
Itzik Ben-Gan Seminars in Sweden and Denmark
It is a privilege to be a part of the same organization with Itzik Ben-Gan. He is one of the best SQL Server authors and speakers in the world today – both because he understands how T-SQL works and he knows how to explain it to an audience. There is much to understand in SQL Server – and especially now with the new opportunities available with Denali.
Itzik is coming to Sweden and Denmark in November to present 1-day seminars about T-SQL. Here is Itzik’s description of his seminar:
You’re a T-SQL developer or DBA and you learned most of what you know about T-SQL by the seat of your pants. You can handle day-to-day T-SQL querying and programming tasks in a reasonable manner, but you’re looking for better and more efficient solutions. This seminar is for you.
The seminar covers common T-SQL querying and programming tasks and shows polished, optimal techniques to handle those. Some of the techniques that you will learn in this seminar are very recent developments. You will learn how to utilize T-SQL constructs available in SQL Server 2008 in creative and efficient ways. You will also get introduced to new T-SQL features planned in the next major release of SQL Server-code-named Denali, and see how you will be able to improve your solutions in the near future.
So I encourage you to consider attending Itzik’s seminars. And, of course, I would like you to attend my seminars (Advanced Analysis Services), too. In Stockholm, we are presenting on the same day – Monday, 7 November. In Göteborg, you can come to both, because Itzik is presenting on Thursday, 10 November, while my seminar is on Friday, 11 November. Then in København, Itzik is on Friday, 11 November, and I am on Monday, 14 November.
Why are we doing these seminars now?
It’s a good time to think about SQL Server and Analysis Services, with the new version of SQL Server coming early next year. There’s a lot of new information available.
But also, we’re doing these seminars to help introduce our new subsidiary – SolidQ Nordic. We want to partner with organizations throughout the Nordic area to help people use SQL Server and Analysis Services more effectively.
Please come! Here’s information on signing up.
Choices in the New World of Microsoft BI
The next version (Denali) of Microsoft SQL Server Analysis Services has a new framework called the Business Intelligence Semantic Model. But look inside the new model, and you’ll see that there are two models sitting side by side – the multidimensional model and the tabular model.
The multidimensional model is familiar to those who have been working with Analysis Services in the past – it’s almost exactly the same as in AS2008 – a few improvements, of course, but very recognizable.
The tabular model is familiar to those who have been working with PowerPivot in Excel 2010 and/or SharePoint. It is based on the same Vertipaq engine.
So how do you choose between them?
You can use both, of course, but there’s not a straightforward way to convert from one to the other. (I’m working with some other folks to build a tool that will help convert back and forth. If you’re interested in that conversion/migration, please let me know.)
If you’re familiar with previous versions of Analysis Services, you’ll probably be more comfortable with the multidimensional world. If you’re working with PowerPivot, you’ve got the tabular.
But what are the advantages of each?
The tabular model is supposed to be easier – no aggregations, partitions appear to be less significant, DAX is supposed to be easier than MDX, working directly with tables in the model is supposed to be easier than creating dimensions and measure groups for database developers who are used to working with tables.
The tabular model can also be faster for querying. It’s faster out of the box, without any configuring. It may be faster in answering certain queries (Distinct Count?) no matter how the multidimensional model is configured. We still have a lot of learning to do about that.
The multidimensional model can handle more data efficiently. The tabular model is limited because it holds all of its data in-memory. That’s an advantage for speed, but not for scalability.
But that’s not the end of the story – because the tabular model has a Direct Query mode, querying the source relational database tables directly as it’s returning data to the end users. For that to work efficiently, there’s relational database optimization to be done – notably indexing – and now, in Denali, columnar indexing.
But we can query the source relational database tables with the multidimensional model, too – we have ROLAP and HOLAP. How are they going to compare with the tabular Direct Query in terms of speed, ease of configuration, and scalability?
And when we start talking about this, there are a lot of other details. DAX (the tabular calculation language) supports a lot of logic, but not as much as MDX in the multidimensional side. And then, in Direct Query mode, some of the best DAX functions aren’t allowed – at least for now!
So there are a lot of things to consider. You can check this all out with the publicly available CTP3 of SQL Server Denali (and, of course, come to my seminars in Scandinavia!) – but I expect it’s going to take all of us some time to learn how to make these choices.
And we also have to try to figure out – how do we expect Microsoft to enhance each of these options in the future?
