Arquivo

Archive for the ‘Mdx’ Category

Using the MDX Script Debugger in BIDS

Pyngback from:https://cwebbbi.wordpress.com/2012/02/21/using-the-mdx-script-debugger-in-bids/#comment-5865

Almost every time I write MDX calculations, I end up using scoped assignments – it’s by far the easiest way to control where your calculations work inside a cube. However making sure that your scoped assignments work in the places that they’re meant to work and don’t overwrite each other can be very tricky indeed (for a brief introduction to the subject and these problems, see this session I gave at SQLBits a year or so ago), so in this post I’m going to show you how you can use the MDX Script Debugger in BIDS to help you do this.

Despite its name the MDX Script Debugger doesn’t actually help you to debug individual MDX expressions or calculations. What it does is clear the MDX Script of the cube then allow you to step through each statement in the MDX Script, applying them one after the other, so you can see the cumulative effect on a query. This is only really useful when you’re working with scoped assignments because it allows you to see which cells in your query are changed with each successive assignment.

To illustrate, let’s use the Adventure Works cube. Comment out everything on the MDX Script (ie what’s on the Calculations tab) except the Calculate statement and add the following code to the bottom:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;

SCOPE(MEASURES.DEMO);        SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);             THIS=2;         END SCOPE;         SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);             THIS=3;         END SCOPE;     END SCOPE;

SCOPE([Measures].[Internet Sales Amount]);        SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);             THIS=2;         END SCOPE;         SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);             THIS=3;         END SCOPE;     END SCOPE;

Then deploy your database and go to Debug menu in BIDS and click Start Debugging:

image

The MDX Debugger screen will then be displayed:

image

In the top pane you can see the Calculate statement highlighted – this is the point in the script that has been reached as you step through the code. In the bottom pane you have a browser control where you can construct a query plus four panes where you can enter your own hand-written MDX queries.

In the browser control, drag the Internet Sales Amount measure onto columns and the Calendar hierarchy from the Date dimension onto rows, until you see something like this:

image

You don’t see any data at the moment because the Calculate statement hasn’t been executed yet (see here for more details on what the Calculate statement does). If you hit F10 to step to the next statement in the MDX Script you’ll see the values for Internet Sales Amount appear because the Calculate statement has now been executed:

image

If you hit F10 again, the calculated member DEMO will be created and you can now drag it into the browser; at this point you’ll see it always returns the value 1 because none of the scoped assignments have been executed yet:

image

Hit F10 again until you reach the first END SCOPE statement and you’ll see the following:

image

You can see that MEASURES.DEMO now returns 2 for the Date, Month and Quarter level as a result of this first assignment; you can also see that only the values that have been affected by this assignment have been changed. Hit F10 some more to execute the second assignment and you’ll see that DEMO returns 3 at the Year level and the affected cells are again highlighted:

image

Notice how, in this case, because you’re scoping on a calculated measure only the cells you scoped on have their values changed. This is in contrast with scoped assignments on regular measures: because regular measures aggregate up, scoping on a regular measure not only affects the values in the cells you scoped on, but those values will then also be aggregated up though the cube.

To show what does happen when you scope on a regular measure, look at the next set of scoped assignments on the Internet Sales Amount measure. The first assignment scopes on the Date, Month and Quarter levels and sets their values to 2; however the Year level values now show the aggregated totals of all the quarters in the year, so if there are four quarters in a year then the year will show 4 * 2 = 8. The All level total is also similarly affected.

image

The final assignment sets the Year totals to 3 for the Year level; this overwrites the values that have been previously aggregated up from the Quarter level, and the Year level values are again aggregated up to the All level:

image

Hit F10 some more and you’ll reach the end of the MDX Script, whereupon you’ll go back to the beginning and can start all this again. Go to the Debug menu and click Stop Debugging to finish. Useful bit of functionality, isn’t it? Certainly one of the least-known features of BIDS too.

One last point – if you try to use the Debugger and hit the infamous SSAS Locale Identifier bug, check out Boyan Penev’s post here on how to solve this issue.

Categorias:Scopes

Error messages in MDX SELECT statements and what they mean – SSMS

Pingback from:

Anyone that has tried to learn MDX will know that, when you make a mistake somewhere in your code, the error messages that Analysis Services gives you are pretty unhelpful. It was suggested to me recently while I was teaching an MDX course that I should blog about common error messages and what they actually mean; so here’s a list of a few example queries using Adventure Works that return confusing errors, the error messages themselves, and details on how to solve the problems. I’ve deliberately concentrated on query-related errors rather than calculation-related errors (that can be a future blog post); if you can think of any more errors that I should cover please leave a comment.

1) Query causing error:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS  [Date].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

Error message: Query (3, 1) Parser: The syntax for ‘[Date]’ is incorrect.

The first step to solving this fairly simple syntax error is understanding the values in brackets in the error message. (3,1) indicates that the error is at character 1 on the third line of the query, where we have the expression [Date].[Calendar Year].MEMBERS; we should also see a red squiggly underneath this text in SQL Management Studio. There’s nothing wrong with this expression though, apart from the fact that it’s in the wrong place: what has happened is that we’ve forgotten to include a comma after COLUMNS immediately beforehand. If we put one in, the query runs.

Solution:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,  [Date].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

2) Query causing error:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS, [Date].[Calendar].[Calendar Year].MEMBERS.CHILDREN ON ROWS FROM [Adventure Works]

Error message: Query (3, 1) The CHILDREN function expects a member expression for the 1 argument. A tuple set expression was used.

This is a very common error that people encounter while learning MDX, and it all comes down to understanding the difference between sets, tuples and members. In a lot of situations Analysis Services is very forgiving: if it expects a set and you give it a single member, then it will cast that member into a set with one item in it for example. It can’t do this for you all the time, though, and you do need to understand what kind of object each function returns and/or expects for a parameter. In this case, the problem is that the .CHILDREN function needs to be passed a member and the .MEMBERS function returns a set (strictly speaking, as the error says, it’s a set of tuples); therefore we can’t use the two functions together. If we want to find all of the children of all years, we can use the DESCENDANTS function instead, which can accept a set as its first parameter.

Solution:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS, DESCENDANTS( [Date].[Calendar].[Calendar Year].MEMBERS , [Date].[Calendar].[Calendar Semester]) ON ROWS FROM [Adventure Works]

3) Query causing error:

SELECT [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] ON COLUMNS, [Date].[Calendar].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

Error message: Parser: The statement dialect could not be resolved due to ambiguity.

Analysis Services supports no less than three query languages: MDX, DMX and a very limited subset of SQL. As a result, when you run a query it needs to work out what query language you’re using and can easily get confused if you make a mistake. In the query above we’ve given a list of the two measures we want to see on the columns axis, but we’ve forgotten to surround this list in braces to turn it into a set – and it’s a set that is required for the axis definition. This is an error that is commonly made by people with a background in SQL, and indeed the problem here is that the error has made the query look a bit too much like SQL or DMX. Putting in braces where they’re needed fixes the problem and removes the ambiguity.

Solution:

SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS, [Date].[Calendar].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

4) Query causing error:

SELECT {[Measures].[Internet Sales Amount1], [Measures].[Internet Tax Amount]} ON COLUMNS, [Date].[Calendar].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

Error message: Query (2, 2) The member ‘[Internet Sales Amount1]’ was not found in the cube when the string, [Measures].[Internet Sales Amount1], was parsed.

A fairly straightforward error this: we’ve tried to reference a member that doesn’t exist in our query – it’s the extra 1 on the end of the name that’s the problem. The way to avoid this is to always let Analysis Services generate unique names for you, and you can do this by dragging the member (or any other object) from the metadata pane in SQL Management Studio into the MDX query pane when you’re writing queries. Here, using the correct member unique name solves the problem.

Solution:

SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS, [Date].[Calendar].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

Note that for dimensions other than the Measures dimension, what happens in this scenario depends on how you’ve set the MDXMissingMemberMode property. By default if you write something that looks like it could be an MDX unique name, but which isn’t actually the unique name of a member on a hierarchy, Analysis Services will simply ignore it. So the following query returns nothing on rows because the year 2909 doesn’t exist in our Calendar hierarchy:

SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS, {[Date].[Calendar].[Calendar Year].&[2909]} ON ROWS FROM [Adventure Works]

And worse, the in this query a genuine syntax error is completely ignored too:

SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS, [Date].[Calendar].[Calendar Year].MAMBERS ON ROWS FROM [Adventure Works]

Categorias:Error Handling, Mdx

How to hide Calculated Members in MDX SSAS

 Link:http://www.bidn.com/blogs/MikeDavis/ssis/2302/how-to-hide-calculated-members-in-mdx-ssas

 

With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.

For example:

Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])

This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time.

Here is how I did this:

Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Semester] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Quarter] or
[Date].[Calendar].level is [Date].[Calendar].[Month]) then
aggregate(ytd([Date].[Calendar].currentmember),[Internet Sales Amount])
else “Use Date Hierarchy”
End

image

Now the user will see the message “Use Date Hierarchy”  if they are not at a level in the correct Hierarchy. The only downside to this is the grand total shows the message instead of the total.

image

If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.

Joining the results of two MDX queries together

Link:http://cwebbbi.wordpress.com/2009/05/20/joining-the-results-of-two-mdx-queries-together/

One question I get asked occasionally is whether it’s possible to join the results of two MDX queries together. Although I seem to remember this kind of functionality is mentioned in the OLEDB for OLAP spec it certainly isn’t supported in Analysis Services MDX and I don’t expect it ever will be; therefore, as all good consultants know, when you’re faced with a request for functionality that doesn’t exist what you have to do is look closely at the requirement to see if there’s a different way of solving the problem to get the result the customer wants…

What people usually want to do when they think about joining MDX queries is this: they want to create a query that shows members from two different hierarchies side-by-side on the same axis. For example, in Adventure Works you might want to see a query with Calendar Years on Rows and Countries followed by Product Categories on Columns, something like this:

Australia Canada Bikes Clothing
CY 2002 $2,154,284.88 $621,602.38 $6,530,343.53 (null)
CY 2003   $3,033,784.21 $535,784.46 $9,359,102.62 $138,247.97

It’s clear we can get the results we need by running two different queries, as follows:

SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

SELECT {[Product].[Category].&[1],[Product].[Category].&[3] }  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

Depending on the tool we’re using, we could try to put the results next to each other to make them more easily comparable. What we can’t of course do is something like the following query:

SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada],[Product].[Category].&[1],[Product].[Category].&[3]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

This will result in the following error message:
Members belong to different hierarchies in the  function.
…for the very good reason that we have violated one of the fundamental rules of MDX – a set has to contain members of the same dimensionality, and here we have a set containing Countries and Product Categories.

What can we do to make the query work? Well, there is a simple MDX solution: create a set of tuples containing Countries and Product Categories:

SELECT
{
CROSSJOIN({[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]},{[Product].[Category].[All Products]}),
CROSSJOIN({[Customer].[Country].[All Customers]},{[Product].[Category].&[1],[Product].[Category].&[3]})
}
ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

What I’ve done here is created a set using two Crossjoins. The first returns a set containing the Countries we want crossjoined with the All Member from Product Categories; the second returns a set containing the All Member from Countries crossjoined with the Product Categories we’re interested in; we can then union them together and use them on the same axis because the tuples in the set have the same dimensionality, ie (Country, Product Category). Here’s what you get back:

image

It’s not quite what we wanted, but it’s all the data we need in a single query and we can probably get the user to ignore the All Members, or possibly hide them in the client tool somehow. The only problem with this approach is that it becomes unwieldy the greater the number of different hierarchies we want to display on columns.

If we’re using SSRS 2008 to display the results of our query, there’s another possible approach: we can use the new Tablix control to create the style of layout we’re after instead quite easily. You need to start by using the query designer and paste in a version of the query above with Years, Countries and Product Categories on Rows and Internet Sales Amount on columns:

SELECT
[Measures].[Internet Sales Amount] ON 0,
{
CROSSJOIN({[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]},{[Product].[Category].[All Products]}),
CROSSJOIN({[Customer].[Country].[All Customers]},{[Product].[Category].&[1],[Product].[Category].&[3]})
}
*
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]

You then create a new matrix, drop Calendar Year onto the row group, Internet Sales Amount into the Data area, Country onto column group, then right click on the rightmost column and select Add Group->Column Group->Adjacent Right, to create a new column group, set it to group by Product Categories and again drop Internet Sales Amount into the data area:

image

Then, for each Column Group you need to make sure that you don’t see aggregated values for the All Members (which of course in SSRS are returned not with the All Member’s name, but with blank names); You do this by setting a filter on each group property, using an expression like:
=Fields!Country.Value IS Nothing
In this case [Country] is the name of the Country in the report, and if this expression returns False we have a Country name and we’re therefore not looking at the All Member.

Anyway, you then get an output like this, which is what we wanted:

image

Here’s one last impractical but fun way to solve the problem. While playing around with DMX recently it occurred to me that the SHAPE statement could also be useful in solving this problem, and a lot of help on the syntax from my friend and DMX (as well as SSIS) guru Mr Allan Mitchell, I came up with the following:

SELECT FLATTENED t.*
FROM
[Sequence Clustering] — arbitrary just has to be a mining model
NATURAL PREDICTION JOIN
SHAPE
{
SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])
}
APPEND
(
{
SELECT {[Product].[Category].&[1],[Product].[Category].&[3] }  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])
}
RELATE [[Date]].[Calendar Year]].[Calendar Year]].[MEMBER_CAPTION]]]
TO [[Date]].[Calendar Year]].[Calendar Year]].[MEMBER_CAPTION]]]
) AS MyNestedTable as t

image

To get this to work you just need to have a mining model in your SSAS database so you can put it in the FROM clause; it doesn’t matter what it is because it’s going to be ignored. I was able to join the queries on the MEMBER_CAPTION field from [Date].[Calendar Year], which contained the names of the Years on rows, although it was a struggle to work out how and where to add all the extra opening and closing square brackets that are needed in the RELATE clause! Notice, though, that we can just paste the MDX queries we need in there – usually SHAPE is used with OPENQUERY, but of course even though this is a DMX query we’re staying within the same SSAS database to get the data so that’s not necessary. Not the most elegant solution, of course, but interesting nonetheless

Categorias:Sets

How do I?

 have seen developers searching for a last minute MDX query / script to create a calculated member on the cube. Many start in an organized fashion with a start from some simple MDX tutorial, and learning tuples, namedsets, axis, MDX Select Query Syntax etc… But still after that, many queries remain unsolved, and the real answer comes after a hands-on experience of some real-time project when one really starts applying functions, tweaking or nesting queries and parameters.
Any material found in MDX should be treasured. By far I have just seen 3-4 books on SSAS flavor of MDX. Below are the links to code-snippets which are FAQ’s or used frequently while either querying the cube or creating calculated members on the cube.

 

Hope this helps all the last minute MDX requirements of those developers who don’t have time to spend in learning all the syntax, structure and theory behing MDX syntax just to solve a little query or requirement on a cube in production or last-minute patch.

 

 

Download MDX Tutorial – Code Snippets of Various FAQ Queries (PDF)

Download MDX Tutorial – Code Snippets of Error Handling (PDF)

Download MDX Tutorial – Cope Snippets SCOPE statement (PDF)

    1. MDX – How do you get Last month in the time dimension
    1. MDX – Query example that returns list of months from start of year up to specified month
    1. MDX – Query to show the first day of the last date month in the cube
    1. How in the report can I order date dimension members in descending order?
    1. MDX – query to get the last Month loaded into a cube
    1. MDX – Query to get the first month of the last year loaded into a cube
    1. MDX – How do you write query that uses execution date/time ( NOW() ) as a parameter?
    1. MDX – Query to get latest months and previous years same months data
    1. MDX – Query to show year level data for all years except last one, and month level data for last year
    1. MDX – How to create calculated member for AVG sales over last 3 years based on NOW()?
    1. MDX – How do I calculate sales for 12 Month to date?
    1. MDX – How can I get Last (Previous) Year to Date (YTD) values?
    1. MDX – Query to get count of months with sales amount > 0 in defined period

MDX – How do you calculate monthly average of one year, optionally including empty months?

  1. MDX – How do you calculate monthly average of one year, optionally including empty months?
Categorias:Functions

What are the popular MDX functions ?

There are many MDX functions. The exact number depends on how you count (are methods and properties are counted, what about operators, are polymorphic functions counted once or as many times are there are overloads, what version of the Analysis Services we are talking about etc), but around 150 is probably right. Of course, not all of them are equally useful. Some seem to pop up in almost every MDX expression (CurrentMember for example), while others are so rarely used, that nobody even know they exist (LinRegR2 anyone ?). I always wanted to find out which functions people really use, and which ones we should not have bothered to add. So I decided to mine microsoft.public.sqlserver.olap newsgroup for answers. First, I thought I would use Google Web API, and even wrote a small C# program which hooked the output of DISCOVER_FUNCTIONS schema rowset with doGoogleSearch SOAP method. However, it turned out, that doGoogleSearch doesn’t work with “group:<newsgroupname>” query term, so I had to send the search queries manually. For each MDX function I checked with Google how many different threads in microsoft.public.sqlserver.olap newsgroup was it mentioned. Note – this is not the number of times it was mentioned, but number of distinct threads. I collected results for some 108 MDX functions, but data required some more filtering/cleaninsing:

  • Some of the MDX function names are also common English words, like Except, Order, Count, Lead etc. Those functions are excluded from statistics.
  • Some other MDX function names are English words commonly used in OLAP terminology, like Filter, Aggregate, Sum, Members, Hierarchy etc. Those functions are excluded from statistics as well.
  • Some MDX functions names are concatenation of two words for similar concepts in Analysis Services, but don’t have space, like VisualTotals, DistinctCount, DefaultMember. Such functions are not excluded from statistics, because cursory look over the threads revealed that the discussion was around the functions.
  • Still there were many MDX functions which also had independent meaning in English and OLAP, like Descendants, Ancestor, Head, Min, Rank etc. I decided to include them (otherwise I would only be counting functions with names like DrilldownMemberBottom – surely cannot be confused with any English word, but how useful is it). The numbers for these functions are probably overrepresented.
  • The frequency of postings into newsgroup doesn’t necessarily indicates how useful the function is, but also how much trouble people have with it. Best example is NonEmptyCrossJoin, which made it into Top 10. Yet I am inclined to think that this is not because everybody uses it, but because nobody understands this function. I have actually never met anybody who fully understands how NonEmptyCrossJoin works. Even I have trouble with it from time to time.

Taking into account all the if’s and but’s above, the results are still interesting. Some things are predictable. CurrentMember is a clear leader, as expected. IIF, Generate, CrossJoin – are all in the top 10 as well. Another expected thing is symmetric functions like PrevMember and NextMember. PrevMember was mentioned 10 times more then NextMember – and this is natural, in calculations we tend to go back in time rather then forward. In fact, I was surprised that NextMember got 17 hits at all. Same story with TopCount vs. BottomCount – who cares about worst ten, we always look at best 10, aren’t we ? Well, I will put both statistics in my report though 🙂 But Head and Tail are going head to head with almost equal ranking. Not sure why is that, given that Head and Tail are used in pretty distinct scenarios. Also, the popularity of Ancestor surprised me, somehow I didn’t deal with it much, but it overtook StrToMember, PrevMember and Lag, which are thought are much more widely used. Overall, results are interesting to look at. Definitely, when choosing which MDX function to tune performance for before we ship AS2005, I am going back to this list.

Functions mentioned in more then 200 threads

MDX Function # threads
CurrentMember 3660
IIF 1340
Descendants 826
Generate 670
Crossjoin 593
NonEmptyCrossJoin 373
Avg 350
Ytd 285
IsEmpty 265
TopCount 211
Functions mentioned in less then 5 threads:

MDX Function # threads
DrilldownMemberBottom 0
DrilldownMemberTop 0
IsSibling 0
LinRegR2 0
LinRegVariance 0
StdevP 0
VarianceP 0
CalculationCurrentPass 1
CovarianceN 1
DrilldownLevelTop 1
DrillupMemebr 1
StddevP 1
StripCalculatedMembers 1
VarP 1
BottomSum 2
DrilldownLevelBottom 2
DrillupLevel 2
LinRegIntercect 3
TupleToStr 3
Covariance 4

Full list can be found here: http://www.mosha.com/msolap/data/MDXFunctionsPopularity.xls

Categorias:Functions

Stairway to Mdx

http://www.sqlservercentral.com/articles/Stairway+Series/Membes+and+Member+functions+in+MDX/73786/

Multidimensional Expressions (MDX) is a standard query language, derived from SQL but geared specifically for OLAP databases. It also includes a calculation language, with syntax similar to spreadsheet formulas. It is an important skill for PowerPivot. Bill’s new series for MDX starts right at the very beginning and takes us through all the basic functions of MDX, with plenty of practical examples.

Stairway to MDX – Level 1: Getting Started with MDX

To learn MDX, there is really no alternative to installing the system and trying out the statements, and experimenting. William Pearson, the well-known expert on MDX, kicks off a stairway series on this important topic by getting you running from a standing start.  Read more…
By Bill_Pearson 2011/03/02 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss   Briefcase | 29,909 reads

Stairway to MDX – Level 2: The Ordinal Function

Business Intelligence Architect Bill Pearson introduces the MDX Ordinal Function, as a means for generating lists and for conditionally presenting calculations. He also demonstrates the use of the function in creating datasets to support report parameter picklists.  Read more…
By Bill_Pearson 2011/02/17 | Source: SQLServerCentral.com | Category: mdx
Rating: |   Discuss   Briefcase | 3,006 reads

Stairway to MDX – Level 3: The Order() Function

The Order() function provides the ‘hierarchized’ sorts you need for reports and applications using MDX. In this Step, Business Intelligence Architect Bill Pearson explores using the versatile Order() function for providing dataset sorts that respect dimensional hierarchies.  Read more…
By Bill_Pearson 2011/05/25 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss   Briefcase | 5,130 reads

Stairway to MDX – Level 4: The Order() Function: Beyond Cube Hierarchies

Bill Pearson continues his examination of the versatile Order() function, focusing upon its use in providing dataset sorts that reach beyond dimensional hierarchies  Read more…
By Bill_Pearson 2011/08/26 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss   Briefcase | 2,570 reads

Stairway to MDX – Level 5: Members, and an Introduction to the MDX Members Functions

Bill explains what is meant by a ‘Member’ and ‘Member function’ in MDX. A member is an item in a dimension that include the ‘measures’ which are the values of the attributes that belong to a dimension. ‘Measures’ are themselves members of a dimension called the “measures” dimension. MDX has a set of functions, known as member functions, each of which allow us to perform operations upon any member of a dimension   Read more…
By Bill_Pearson 2011/09/02 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss   Briefcase | 1,278 reads

Stairway to MDX – Level 6: Member “Family” Functions: .Parent and .Children

Some of the most important ‘Member’ functions of MDX are often known as the ‘Family Functions’. These are used in queries on multidimensional structures for analysis and reporting, and can refer to relative nodes in the hierarchy in much the same way as in a family tree. Read more…
By Bill_Pearson 2011/09/09 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss   Briefcase | 312 reads

Stairway to MDX – Level 7: Member “Family” Functions: Ancestor() and .FirstChild

BI Architect Bill Pearson continues his introduction to the MDX Members functions. In this Level we continue our exploration of the general “family” group, with the Ancestor() and .FirstChild functions.  Read more…
By Bill_Pearson 2011/09/23 | Source: SQLServerCentral.com | Category: stairway series
Rating: (not yet rated) |   Discuss   Briefcase | 197 reads

Stairway to MDX – Level 8: Member “Family” Functions: .LastChild and .FirstSibling

BI Architect Bill Pearson continues his introduction to the MDX Members functions. In this Level we continue our exploration of the general “family” group with the .LastChild and .FirstSibling functions.  Read more…
By Bill_Pearson 2011/10/14 | Source: SQLServerCentral.com | Category: stairway series
Rating: (not yet rated) |   Discuss   Briefcase | 159 reads

Stairway to MDX – Level 9: Member “Family” Functions: .LastSibling and Cousin()

BI Architect Bill Pearson concludes his introduction to the MDX Members functions. In this Level we wrap up our exploration of the “family” group with the .LastSibling, and Cousin() functions.  Read more…
By Bill_Pearson 2011/10/26 | Source: SQLServerCentral.com | Category: stairway series
Rating: (not yet rated) |   Discuss   Briefcase | 186 reads
Categorias:Learning