MDX # 40 – CROSS JOIN does not return Cartesian product of two hierarchies from the same dimension

April 15, 2014 Leave a comment

Cartesian product can be illustrated by a Cartesian square

Most SQL developers are familiar with the concept of CROSS JOIN and Cartesian product. Cartesian product is the result that is produced from a CROSS JOIN.

In this Wikipedia article, Cartesian product, a Cartesian product is illustrated by a Cartesian square as shown below.

Cartesian Square

 

 

 

 

 

 

 

In SQL Server, CROSS JOIN returns the Cartesian product of rows from tables in the join

Both of the following articles claimed that the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

The above definition of the SQL CROSS JOIN can be illustrated with the following SQL script using the AdventureWorksDW database.

use AdventureWorksDW
go

with color as (
select      distinct Color
from  dbo.DimProduct
),

sub as (
select      distinct EnglishProductSubcategoryName
from  dbo.DimProductSubcategory
whereEnglishProductSubcategoryName = ‘Bib-Shorts’
)

select      *
from  sub CROSS JOIN color
order by
EnglishProductSubcategoryName, Color
–10

To simplify the result set, I am using only one product subcateogry ‘Bib-Shorts’. The above SQL script generates the results shown in the screenshot below. With 1 product category and 10 product colors, the result set is the every possible combination of these two lists, which is 10 (1 * 10) rows.

Multi-Bib-shorts

 

 

 

 

 

 

 

 

 

 

Keep in mind that the above 10 combinations of the colors and the subcategory do not necessarily have any sales. In order to find witch combinations have sales, we will need to do an INNER JOIN on these 3 tables:

dbo.FactResellerSales or dbo.FactInternetSales
dbo.DimProductSubcategory
dbo.DimProduct

The following 2 SQL queries can be used. It turned out that there is no internet sales for multi color bib-shorts, but there are reseller sales for them.

– There is no Internet Sales for Multi color Bib-Shorts
select      *
from  dbo.FactInternetSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–0

– There is 756 Reseller Sales for Multi color Bib-Shorts
select      *
from  dbo.FactResellerSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–756

In MDX, CROSS JOIN does not return the Cartesian product members from hierarchies

Enter the world of MDX. CROSS JOIN is an important operation in MDX, with some twists and turns.

The MDX query below is the equivalent of the SQL CROSS JOIN in the previous section. However, the result set contains only one row.

select
{ } on 0,
{ CROSSJOIN(
[Product].[Subcategory].[Subcategory].[Bib-Shorts],
[Product].[Color].[Color].members
)
} on 1
from
[Adventure Works]

Multi-Bib-shorts Crossjoin

 

 

 

 

 

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best.

Please refer to the section ‘Finding related members in the same dimension“ in Chapter 5 of our book, MDX with SSAS 2012, to gain more knowledge on this topic and how it is related to the concept of auto-exists and the EXISTS and EXISTING function.

Three different ways to do crossjoining

In MDX, you can cross join different hierarchies using three different ways. Chris Webb has a blog here, The rather-too-many ways to crossjoin in MDX, which is better than anything I can write about CROSS JOIN.

 

Categories: MDX Challenges

MDX # 39 – Find Default Measure using MDX Query

April 15, 2014 Leave a comment

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.

For more information about how to define a default member for hierarchies and whether  you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:

Define a Default Member

MDX script is an easy place to maintain the default member definition

As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.

Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.

/*– Set default member for the Destination Currency cube dimension —-*/

Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

MDX query is also an easy place to find what the default members are for each hierarchy

As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:

DefaultMember (MDX)

The general syntax is:

Hierarchy_Expression.DefaultMember

Three Sample MDX queries to find default members for hierarchies and cube default measure

The following are three examples to find default members for hierarchies and the cube’s default measure.

  •  Find Default Member of Destination Currency hierarchy

WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
– Default member for Destination Currency hierarchy: US Dollar

  • Find Default Member of Geography hierarchy

WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
– Default member for Geography hierarchy: All Geographies

  • Find Default Measure of the Adventure Works cube

WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name

SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
– Default cube measure: Reseller Sales Amount

Showing default measure and the value in one query

By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.

SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]

Reseller Sales Amount
$80,450,596.98

 

 

MDX+SSAS #37–Ignore unrelated dimension or not

February 7, 2014 1 comment

In Adventure Works cube 2008 or 2012, in the Dimension Usage tab, you will see that many intercepts of measure groups and dimensions are blank. To a specific measure group, the dimensions that have blank intercepts are un-related to the measure group. As a matter of fact you will see blank intercepts more often than not.

As the term un-related dimension suggests, these measure groups have no relationship to these dimensions. SSAS, however, gives us a measure group property IgnoreUnrelatedDimensions (true or false) to control how we want these measures to show for those un-related dimension members.

IgnoreUnrelatedDimensions is a boolean property, so how hard it can be. It turned out that this property is not as straightforward as it seems, and it might not be as effective as you would think either.

When IgnoreUnrelatedDimensions = True, the measure repeats for all members and all levels on the un-related dimensions

IgnoreUnrelatedDimensions = True is a default setting for measure groups. The measure behavior is not the best (actually very confusing to users), but the behavior is consistent and easy to remember.

When IgnoreUnrelatedDimensions = True,

  • the measure will repeat with the same value for all member for all level for any unrelated dimension,
  • regardless if the dimension has default member or not, root [All] member or not
  • this behavior is the same as VALIDMEASURE() function which also repeats the measure with the same value for all member for all level for any unrelated dimension
  • Example 1:

  • Exchange Rates measure group is not related to the Promotion dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Promotion has no default member

image

    Example 2:
  • Exchange Rates measure group is not related to the Scenario dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Scenario has a default member of Actual

image

    Example 3:
  • Exchange Rates measure group is not related to the Sales Channel dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Sales Channel IsAggregatable is set to False; no root [All] member

image

When IgnoreUnrelatedDimensions = False, the measures behave differently depending on if  the un-related dimension ….

…has default member or not, has root level [All] member or not.

Example 4:

  • Sales Target measure group is related to the Date dimension but only at Calendar Quarter level
  • (I’ve disable the calculation in the MDX Script "Sales Quota Allocation" which allocates values down to the Month level)
  • The measure shows at the Quarter level and rolls up to all the ancestor levels.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all months and days 
  • image

Example 5:

  • Sales Target measure group is not related to the Promotion dimension
  • Promotion dimension has a [All Promotions] root level member
  • The measure shows for the [All Promotions] member only.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all members

image

Both Example 4 and 5 produce an easy to remember result, that is, the setting forces the measure to the top root member and the member that the measure is linked to and al the ancestor members from that point on.

However, Example 6 and 7 is a bit confusing, because the setting of IgnoreUnrelatedDimensions = False is totally ignored, just because the un-related dimension

  • has a default member, or
  • has no root [All] member

Example 6:

  • Sales Target measure group is not related to the Scenario dimension
  • Scenario dimension has a default member Actual
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Example 7:

  • Sales Target measure group is not related to the Sales Channel dimension
  • Sales Channel IsAggregatable is set to False; no root [All] member
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Reporting Driven Decision – relate or un-relate

I recently worked on a request to pull some measures on a dimension that is unrelated to the measure group.

Although the original cube design didn’t link the measure to the dimension, I can see that the design might have missed the reporting requirement. I had a few choices to meet the new reporting requirement:

  1. IgnoreUnrelatedDimensions = False
  2. Creating a MDX calculation to show the measure for the appropriate members on the un-related dimension.
  3. Linking the measure to the dimension in the relational data warehouse, not to the leaf level members but to a middle level, similar to how the example 4 where the Sales Target measure group is related to the Date dimension but only at Calendar Quarter level.
    After some experiment, I’ve abandoned both option 1 and 2, opted for option 3.
    This blog explains why I didn’t choose option 1. The IgnoreUnrelatedDimensions setting for measure groups is not a very reliable way to meet my reporting requirement with so many moving parts.

Option 2 seemed very attractive to me at first. The reason that I didn’t go for it will be topic for another blog.

When SSAS is not enough

After running the above queries with different IgnoreUnrelatedDimensions setting for different scenarios, I recalled a Chapter in our book MDX Cookbook “When MDX Is Not Enough”. Here I’d like to say that sometimes settings in SSAS can seem to provide simple solutions but are actually not enough.

SSIS #116–What is the variable value at runtime?

January 29, 2014 Leave a comment

Using variables in SSIS packages can make your ETL process more dynamic and respond to different scenarios during runtime.

Not only we can use variables, we also have many different ways to set values for our variables. This is all good news for developers. But like many things in real life, when we have too many choices, we can get carried away and overcome by our enthusiasm. This is especially true in terms of variables in SSIS packages.

5 different ways to set values for variables

In general, we have the following 5 different ways to set values for variables. The question we need to ask is what is the order of precedence during runtime when we are using all these 5 different ways to set values for a variable.

  • In the Variable Editor in the Value field: although varCubeName is a String type, we do not need to use the double quotes to enclose the value Cube Name in Value.

image

  • In the Variable Expression Editor: variable values can also be set in the Expression field. We must enclose the value in double quotes. This is true even for Integer type variable. Notice that the value in the Value field has changed from Cube Name in Value to Cube Name in Expression. Once I entered an expression, both the Value and the Expression field has become the same.

image

  • In the package configuration file: I always prefer an XML configuration file, in which I set varCubeName to a value of Cube Name in Package Configuration.
  • image

  • In many control flow tasks, such as an Execute SQL Task, and some data flow transformation tasks. for the purpose of this blog, I picked the Execute SQL Task to set the value for varCubeName to Cube Name from Execute SQL Task during runtime.
  • In Script Task, I can also modify the value of varCubeName. For the purpose of this blog, I set it to Cube Name in Script Task in a scrip task.
    To summarize, here are the values I set for the variable varCubeName using 5 different ways:
  1. Cube Name in Value: in the Variable Editor in the Value field
  2. Cube Name in Expression: in the Variable Editor in the Expression field
  3. Cube Name in Package Configuration: in the package configuration file
  4. Cube Name from Execute SQL Task: in the Execute SQL Task
  5. Cube Name in Script Task: in a scrip task

4 and 5 are essentially the same, so I’ll just test it one at a time. In my first test, I disabled the scrip task because I only want to test 1, 2, 3, and 4. I want to see what the value for my variable varCubeName really is at runtime. I enabled the package configuration so I can throw #3 in the midst.

I used two script tasks with the MessageBox function to show me the value at runtime, both before and after the Execute SQL Task.

image

 

“Cube Name in Expression” took precedence over all other values

Before the Execute SQL Task…

image

After the Execute SQL Task…

image

The value is always Cube Name in Expression. It’s like the Execute SQL Task had never even executed.

“Cube Name in Package Configuration” took precedence over Cube Name in Value, but gives way to “Cube Name from Execute SQL Task”

Now we know that Cube Name in Expression has the highest precedence order. Let’s remove it and only leave the Cube Name in Value, as shown in the following screenshot.

image

Before the Execute SQL Task…

image

After the Execute SQL Task…

image

This time we see that the Cube Name in Value which is set in the Variable Editor in the Value field never showed up. Cube Name in Package Configuration and Cube Name from Execute SQL Task both overwrite the Cube Name in Value.

Enabling the Scrip Task produces similar results, in which “Cube Name in Script Task” has the same precedence order as the “Cube Name from Execute SQL Task”.

Conclusion on the precedence order

  1. Cube Name in Expression: the value is set in the Variable Editor in the Expression field. The value set in this way has the highest precedence order and overwrites the values in all other ways.
  2. Cube Name from Execute SQL Task: the value is set in the Execute SQL Task. It has the second highest precedence order. Cube Name in Script Task has the same precedence order.
  3. Cube Name in Package Configuration: the value is set in the package configuration file. It can only overwrites the variable’s default value.
  4. Cube Name in Value: the value is set in the Variable Editor in the Value field. This value is usually called the default value, because it can be overwritten by all the proceeding ways during runtime.

Lessons learned

During the SSIS package deployment, your DBA decided to include all the variables along with all the connection strings as the property/value pairs in the package configuration files, and somehow your packages are not working the way you had expected.

During development, your packages are not working the way you would expect and you decided to debug your variables and found out that the variable value at runtime was incorrect.

The above two scenarios happened to me in the past and they have inspired this blog.

The lessons I’ve learned are:

  • During deployment, do not include variables property/value pair in the package configuration file. Variables should only be handled in the SSIS packages.
  • If variable expression is sufficient, use variable expression only.
  • If you need to combine different ways to set variable values at runtime, only use these two combinations.
    Default value +  Execute SQL Task, or

    Default value +  Script Task

Note

  • All screenshots are from Integration Services 2012.

SSRS #73 – Marlon Ribunal and Mickey Stuewe’s New Book: Reporting Services 2012 Blueprints

January 24, 2014 Leave a comment

The book’s link is here, SQL Server 2012 Reporting Services Blueprints.

This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you already have a basic knowledge of how data source and datasets in SSRS work, this book will advance your reporting skill to the next level.

In order to accomplish all the steps discussed in this book, including the steps on how to integrate SharePoint 2010/2013, PowerPivot, and Power View with SSRS, it is recommended that you use SQL Server 2012 Enterprise or Business Intelligence Edition. You can still use the Standard Edition to accomplish most of the steps in the book but it doesn’t support the advanced BI features, such as PowerPivot and Power View.

To create SSRS 2012 reports, you have two options:

  • You can use the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT
    from the SQL Server installation media.
  • You can also use Visual Studio 2012. If you use Visual Studio, you must install the SSDTBI templates. SSDTBI (SQL Server Data Tools-Business Intelligence) is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.

To try the examples in this book, you will need to have 2012 AdventureWorks Sample database.

In Chapter one you will learn right away different tricks on how to implement parameters for  
different scenarios, ability to exclude one or more parameters at runtime, drop-down parameter, multivalued parameter, cascading parameter and cascading multivalued parameter.

I really appreciate Chapter 2 in which a section is devoted to create custom report template. Report templates not only save time but also creates visual consistency that suit your organization branding. Number crunching is really all about data summaries, aggregates, and groupings. Through practical examples in Chapter 2, you will be able to pick up the skills very quickly.

Another essential skills in creating SSRS reports is to be able to add interactive elements, such as Actions, Tool tips, Document Map, Sorting and Bookmark to a report. In SSRS, one report can also interact with another report through Subreport, Drillthrough report or linked report. You will absolutely love Chapter 3 which focuses on adding actions to SSRS reports. 

Have you ever wondered if SSRS is also a good tool for data visualization? In SSRS 2012, in the report designer toolbox, you will find a few report items that are new, they are, Data Bar, Sparkline, Indicator and Map. Chart and Gauge exist in the prior versions.

Chapter 4 will show you how to create reports in SSRS 2012 with data bars, sparklines, gauges, and indicators. Next time if you need to meet the common data visualization requirements, you will find that this book can make your data visualization work seem effortless.

I particularly like Chapter 5 which goes right into map visualization. It not only shows how to use the built-in maps for the United States, but also how to import and use commercial shapefiles (map files) to create world map report.

If you are tasked to develop reports based on the cubes in the OLAP, Chapter 6 will show you how to survive simple implementation just with the query designer in SSRS.

The last chapter in this book goes beyond just report development. It will show you how to get your SSRS reports ready for production. You will find practical information on deployment configurations, user security, shared schedules, cached reports, and snapshots. It also dives into report subscriptions with e-mail delivery / scheduled delivery and data-driven subscription.

Overall, this SSRS 2012 blueprints book is a well written book and worth buying if you just start out developing SSRS reports in 2012. If you started with the prior versions and already are an experienced report developer, then this book might not be right for you. However, if you are like me and new to the data/spatial visualization features in SSRS 2012, you will find very useful information in this book on how to use the data bars, sparklines, gauges, and indicators and map objects.

Congratulations to Marlon Ribunal and Mickey Stuewe on their first SSRS book. And to reviewers Basit A. Masood-Al-Farooq, Varinder Sandhu, SIVAKUMAR VELLINGIRI and Valentino Vranken. Their combined experience in SQL Server Reporting Services report development makes this book a valuable addition to every SSRS developer’s library.

2013 in review

January 1, 2014 Leave a comment

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 120,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 5 days for that many people to see it.

Click here to see the complete report.

Follow

Get every new post delivered to your Inbox.

Join 159 other followers