Transcript BDBI - Common Ground Solutions

BDBI Radio
Baker’s Dozen Business Intelligence Webcast Radio
13 Weekly tips on Microsoft SQL Server/BI/SharePoint Technologies
http://www.BDBIRadio.com
Week of 03/24/2013: 13 Tips for using MDX in OLAP Applications
BDBI Radio
• Produced and Hosted by Kevin S. Goff, Microsoft SQL
Server MVP and author of CoDe Magazine “Baker’s Dozen”
Productivity Series
• Pre-recorded Weekly webcast – Available every Sunday
• Site: http://www.BDBIRadio.com
• My overall blog/site: http://www.KevinSGoff.net
• Program format:
– 13 quick tips on different SQL/BI/SharePoint topics
• In any week, tips might cover one topic (13 features in PowerPivot,
13 SQL Interview topics), or 13 random tips on anything from data
warehousing to MDX/DAX programming, to maybe even a few .NET
tips for scenarios when the .NET and BI worlds collide
– (Email me at [email protected] if you have questions)
7/17/2015
13 tips for MDX in OLAP apps
2
BDBI Radio
• Upcoming community events where I’ll be speaking (confirmed):
– SQL User Group in NYC on Thursday night, April 25
• http://nycsqlusergroup.com/
New Features in SSIS 2012
– Northern Virginia SQL User Group on Monday night, May 20
• http://www.novasql.com
(might be related to Reporting Services)
• Possible Speaking Dates:
–
–
–
–
Philadelphia SQL Saturday in Malvern PA, June 1st
Baltimore SharePoint Saturday, May 18th
Washington DC SharePoint Saturday, June 8th
NYC SharePoint Saturday, July 27th
• Other News:
– Big News! SQL Server Data Tools for Visual Studio 2012!
• http://blogs.msdn.com/b/analysisservices/archive/2013/03/06/sql-server-data-toolsbusiness-intelligence-for-visual-studio-2012-released-online.aspx
– SQL Server 2012 Service Pack 1 available
• http://blogs.msdn.com/b/analysisservices/archive/2012/11/07/announcingmicrosoft-sql-server-2012-service-pack-1-sp1.aspx
7/17/2015
13 tips for MDX in OLAP apps
3
BDBI Radio
• Today’s show: 13 uses for MDX in OLAP Applications
• MDX is the programming language used for SSAS OLAP (multidimensional)
databases
• Many OLAP applications out there, many will still be built in the next few
years
• Future show will look at DAX programming in SSAS Tabular
• MDX can be difficult to learn, but often necessary in OLAP applications
• We’ll look at examples for parameter management, and instances where the
built-in OLAP query designers don’t offer enough functionality
• If you work with OLAP databases, knowing MDX is the difference between the
“casual” OLAP developer (who might only know how to use the cube wizard)
and a professional OLAP developer who will have better odds of landing
SSAS/OLAP positions.
7/17/2015
13 tips for MDX in OLAP apps
4
BDBI Radio – Topics for today
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SSRS – a report showing a 12 month moving average
SSRS – issuing a RANK statement across columns
SSRS – Sales for a day and prev. day (where prev day could be X days ago)
***SSRS – Generating output for the top X cities w/in the Top Y Products
SSRS – Dealing with an SSRS 2008/2008R2 issue with OLAP parameters
SSRS – Parent/Child reports – “stuffing” OLAP parameters values
SSRS – Using MDX Named Sets defined back in the OLAP cube
PPS – Using MDX to customize the rows/column content of a KPI scorecard
PPS – Implementing Cascading Parameters
PPS – Using MDX to customize the output of an Analytic Grid
PPS – Using MDX Named Sets defined back in the OLAP cube
Excel - using a free add-in to incorporate MDX snippets into a PivotTable
.NET – Passing MDX code from a .NET application
7/17/2015
13 tips for MDX in OLAP apps
5
1 - Showing a 52 week moving average in SSRS
7/17/2015
13 tips for MDX in OLAP apps
6
1 - Showing a 52 week moving average in SSRS
1. Can use the OLAP query designer, but must add 2 calculated
members to determine 12-month avg
7/17/2015
13 tips for MDX in OLAP apps
7
1 - Showing a 52 week moving average in SSRS
2 different ways to calculate 12 month average, using MDX
LASTPERIODS function. One uses an AVG (that ignores NULLs) and
a second that uses a SUM and divides by 12
7/17/2015
13 tips for MDX in OLAP apps
8
1 - Showing a 52 week moving average in SSRS
•
•
•
Avg Sales from July 2005 to June 2006
Avg Sales from Aug 2005 to July 2006
Avg Sales from Sep 2005 to Aug 2006
Notice how the 12 month
“range” continues to move with
each new month.
Why it’s called a “Moving
Average”
•
•
Anytime we want to create a
range of time periods “going
back”, where the end of the
range is dynamic, we can use
LastPeriods
Take the example of a moving
average – for a 12 month
period, we want to know the
average sales going back over
that 12 month range
We can use LASTPERIODS to go
back (X) number of months,
based on the current month
(which moves/changes
throughout the month)
We could also use the LAG
function to explicitly create a
range
Note that when determining
the 12 month range, the
formula does not include the
current month in the range.
(That would be a business
call).
1 - Showing a 52 week moving average in SSRS
Issue with Named Sets
•
The named set does not
“pick up” the change in
CurrentMember for each
of the 12 months!!!
•
•
These are null because
the named set never
sees the change in
CurrentMember. It
never “reads” any
month, so the
calculation of AVG is
over a null set of
members,
Only recourse is to
“embed” the date range
in the calculated member
(previous slide)
•
•
•
Notice how we tried to take the
range of months (using
LastPeriods) and place it in a set
Then try to reference the set in
the calculated member
aggregation
This would seem to be nothing
more than “shifting things
around”, but actually has a
significant impact on the query
Remember – named sets do not
pick up context beyond the where
clause
However, the concept/context of
[Date].[Fiscal].CurrentMember
changes on a row-to-row based –
and unfortunately, a named set
does not “keep up”.
Again, a named set will not work if
it’s expected to “change it’s
contents” throughout the query
2 - An MDX Rank across columns in a PIVOT Table
7/17/2015
13 tips for MDX in OLAP apps
11
2 - An MDX Rank across columns in a PIVOT Table
MDX code for the
report uses the
ORDER and
FILTER to Rank
against a named
set.
Also uses EXISTS
to rank each
subcategory
within a Country
7/17/2015
13 tips for MDX in OLAP apps
12
2 - An MDX Rank across columns in a PIVOT Table
with set [OrderedCountriesSubCategories] as
ORDER(
filter( [Customer].[Country].Children * [Product].[SubCategory].Children,
[Internet Sales Amount] <> null),
[Internet Sales Amount], DESC)
member [SalesRank] as
RANK( ( [Customer].[Country].Currentmember,
[Product].[SubCategory].CurrentMember),
EXISTS( [OrderedCountriesSubCategories],
[Customer].[Country].CurrentMember) )
select { [Internet Sales Amount], [SalesRank] } on columns,
[OrderedCountriesSubCategories] on rows
from [Adventure Works]
7/17/2015
13 tips for MDX in OLAP apps
13
3 - Sales for a Day and prev day (going back X days)
This goes beyond just using PREVMEMBER to get sales for prior day
Will go back as far as 7 days
7/17/2015
13 tips for MDX in OLAP apps
14
3 - Sales for a Day and prev day (going back X days)
Uses MDX query parameters and references them in the MDX code
7/17/2015
13 tips for MDX in OLAP apps
15
3 - Sales for a Day and prev day (going back X days)
WITH MEMBER [SalesPriorBusinessDay] as
([Internet Sales Amount],
tail( filter( { [Date].[Date].CurrentMember.Lag(7) :
[Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'
MEMBER [Measures].[LastSaleDate] AS
tail( filter( { [Date].[Date].CurrentMember.Lag(7) :
[Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0).name
select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on
columns,
order(filter( [Customer].[City].Children, [Internet Sales Amount] > 500),
[Internet Sales Amount], desc) on rows
from [Adventure Works]
where strtomember( @DateSelection)
7/17/2015
13 tips for MDX in OLAP apps
16
3 - Sales for a Day and prev day (going back X days)
Report allows
us to get the
top Y selling
products, and
for each
product, give
us the top N
cities
Uses the MDX
GENERATE
function
7/17/2015
13 tips for MDX in OLAP apps
17
3 - Sales for a Day and prev day (going back X days)
7/17/2015
13 tips for MDX in OLAP apps
18
3 - Sales for a Day and prev day (going back X days)
with set [TopNProducts] as topcount( [Product].[Product].Children, @TopNProductCount,
[Internet Sales Amount])
set [TopCitiesWithinTopProducts] as
GENERATE ( [TopNProducts],
( [Product].[Product].CurrentMember,
TOPCOUNT( [Customer].[City].Children, @TopNCityCount, [Internet Sales
Amount] ) ) )
member [CityRank] as
RANK( ( [Product].[Product].CurrentMember, [Customer].[City].CurrentMember),
exists( [TopCitiesWithinTopProducts], [Product].[Product].CurrentMember))
member [Product Rank] as RANK( [Product].[Product].CurrentMember, [TopNProducts])
select { [Internet Sales Amount], [Product Rank], [CityRank]} on columns,
[TopCitiesWithinTopPRoducts] on rows
from [Adventure Works]
where strtoset( @FiscalYear)
7/17/2015
13 tips for MDX in OLAP apps
19
4 - Top N cities by Sales within Top Y Products by Sales
Report allows
us to get the
top Y selling
products, and
for each
product, give
us the top N
cities
Uses the MDX
GENERATE
function
7/17/2015
MDX in OLAP Applications
20
4 - Top N cities by Sales within Top Y Products by Sales
7/17/2015
MDX in OLAP Applications
21
4 - Top N cities by Sales within Top Y Products by Sales
with set [TopNProducts] as topcount( [Product].[Product].Children, @TopNProductCount,
[Internet Sales Amount])
set [TopCitiesWithinTopProducts] as
GENERATE ( [TopNProducts],
( [Product].[Product].CurrentMember,
TOPCOUNT( [Customer].[City].Children, @TopNCityCount, [Internet Sales
Amount] ) ) )
member [CityRank] as
RANK( ( [Product].[Product].CurrentMember, [Customer].[City].CurrentMember),
exists( [TopCitiesWithinTopProducts], [Product].[Product].CurrentMember))
member [Product Rank] as RANK( [Product].[Product].CurrentMember, [TopNProducts])
select { [Internet Sales Amount], [Product Rank], [CityRank]} on columns,
[TopCitiesWithinTopPRoducts] on rows
from [Adventure Works]
where strtoset( @FiscalYear)
7/17/2015
MDX in OLAP Applications
22
5 – SSRS OLAP parameter issue in SSRS 2008/2008R2
Right-click to
show hidden
DataSets
• An issue in SSRS 2008/2008R2 (addressed in SSRS 2012)
• Suppose we create a simple report against an OLAP cube, and
build a basic parameter to allow a user to select a Country
• SSRS generates a hidden dataset that retrieves the list of countries
7/17/2015
13 tips for MDX in OLAP apps
23
5 – SSRS OLAP parameter issue in SSRS 2008/2008R2
• The MDX code in the “hidden” dataset brings back the ALL
member total – suppose we don’t want it?
• We can modify the MDX code
7/17/2015
13 tips for MDX in OLAP apps
24
5 – SSRS OLAP parameter issue in SSRS 2008/2008R2
• We run the report, and
everything is good
• Then we go back to the
original dataset and
make a change – maybe
we need a second
measure. Innocent
enough, right?
7/17/2015
13 tips for MDX in OLAP apps
25
5 – SSRS OLAP parameter issue in SSRS 2008/2008R2
• We add the measure to the report….
• But the dropdown parameter is back to showing the original list of
countries.
• And if we go back to the hidden dataset that generates the list of
countries, the MDX code is back to the original code. In other words, our
MDX changes were overwritten!!! Not good!!!
7/17/2015
13 tips for MDX in OLAP apps
26
5 – SSRS OLAP parameter issue in SSRS 2008/2008R2
• This behavior occurs in SSRS 2008/2008R2 (it is fixed in 2012)
• The problem is that when we went back to the main dataset and added a
column, BIDS “re-generated” the dataset for the Country parameter – and
overwrites our MDX code (when we modified the dataset manually)
• There is a workaround:
• While BIDS does not provide any mechanism/option in the BIDS interface to
“protect” our MDX code override in a generated dataset….
• However, we can protect the MDX code in the dataset by opening the report
RDL file directly and entering a SuppressAutoUpdate tag for each DataSet
where you entered MDX manually (this is the equivalent of protecting the
DataSet)
Must define
SuppressAutoUpdate
for each generated
dataset where you
entered MDX code to
override the original
generated code
7/17/2015
13 tips for MDX in OLAP apps
27
6 – SSRS Parent/child Reports with OLAP Parameters
• This one definitely falls under the category of “needle in a
haystack”
• Sometimes we might need to pass OLAP parameters from one
report to another (as a parent-child report action) – maybe the
child report excepts OLAP parameters from the parent, or maybe
the child report is a relational report, and needs to take the OLAP
parameter value and convert to just a regular key value
• This can be a bit tricky – and sometimes requires a little “elbowgrease” – but it’s usually resolvable
• There’s no one pattern here – this can happen in several different
scenarios, but here are a few tips that might help…
7/17/2015
13 tips for MDX in OLAP apps
28
6 – SSRS Parent/child Reports with OLAP Parameters
•
•
•
In the one on the left, a result set might need to materialize each state name in
[dimension].[Attribute].[Key Value] format, by creating a Calculated Member with the
expression [Geography].[State-Province].CurrentMember.UniqueName
In the example on the right, we need to pass a report Parameter value to a child report. The
original parameter might be stored as “CY 2006” and we might only need the value of “2006”
A variation of the second one might involve the reverse of the first one….in other words, we
might need to take [Geography].[State-Province].&]CA]&[US] and extract just the value of CA
from it. Might need to use the SSRS REPLACE function, along with other string function as well
7/17/2015
13 tips for MDX in OLAP apps
29
7 – SSRS reports with OLAP Named Sets
• Suppose we have a basic named set in the OLAP cube that returns the 10
best cities based on Reseller Sales
• On the right, I’ve used it in a test query
• Can we use this in SSRS reports, anytime we want to show the top 10 cities?
• The answer is “yes”, though it’s a bit more involved than you might think
7/17/2015
13 tips for MDX in OLAP apps
30
7 – SSRS reports with OLAP Named Sets
•
•
•
•
•
In the SSRS designer, we
see the Named Set
Reference under the
Geography Dimension
But we can’t drag it into
the ROWS axis – the
designer doesn’t permit it
The only way to use the
designer is to drag the
named set into the
dimension slicer at the
top, and then select the
City attribute in the rows
Not a great workaround,
as the rows are ordered
by city name, and not by
Reseller Sales Amount.
The report body would
need to order the rows
Alternative is to write
code (see next slide)
7/17/2015
13 tips for MDX in OLAP apps
31
7 – SSRS reports with OLAP Named Sets
•
•
•
•
The alternative is to toggle
the toolbar option in the
top right hand side, to go
into “MDX” mode
Here, you can reference
the named set directly in
the ROWS axis
Remember that to
reference the parameters,
you must use
STRTOMEMBER (if the
user can only make one
selection for the
parameter) or STRTOSET
(if the user can make
multiple selections for the
parameter
If the parameters were
not previously defined in
the designer, must create
them manually
7/17/2015
13 tips for MDX in OLAP apps
32
8 - Using MDX snippets in a KPI dashboard
KPI Dashboard that receives a Salesperson as a parameter,
and displays that salesPerson and the hierarchical children
(one level down) across the columns axis of the scorecard
7/17/2015
13 tips for MDX in OLAP apps
33
8 - Using MDX snippets in a KPI dashboard
•
•
•
Create a filter connection link between the Employee Filter and the scorecard
Scope the target of the filter link to the scorecard columns
Enter the MDX snippet, <<UniqueName>>, <<UniqueName>>.Children in the Connection
Formula
34
9 – Cascading Parameters in PPS
•
We want to select a product subcategory from a list of those subcategories with sales, ordered by sales
•
Based on the selected subcategory, we want to show just those Countries/State-Provinces/Cities that have
had reseller sales for the selected subcategory (only 5 of the 6 German provinces is showing)
•
And from there, after the user selects a geography, only show the Fiscal Years where Reseller sales have occured
7/17/2015
13 tips for MDX in OLAP apps
35
9 – Cascading Parameters in PPS
7/17/2015
•
PPS MDX Filter to only show those
product subcategories with Reseller
Sales, ordered by Reseller Sales
•
PPS MDX Filter to return the levels
between Country and City
(inclusive), using DESCENDANTS –
but only for those geographies that
have sales. But how does it “read”
the Product Subcategory selection,
to get the cascading effect? We do
that in the filter link (on the
dashboard page)
•
PPS Filter to filter out the First and
Last Year (just to show we can do it)
and then only return those years
where sales exist. Again, the
cascading effect will be
implemented on the dashboard page
13 tips for MDX in OLAP apps
36
9 – Cascading Parameters in PPS
•
•
•
7/17/2015
13 tips for MDX in OLAP apps
We can create a filter
connection between
the list of
subcategories and the
list of geographies.
The MDX code for the
list of geographies will
pick up the “context”
of the filter selection
for the subcategories
Same deal for the list
of years – we can
create a filter
connection between
the geography filter
and the list of years.
37
10 - Using MDX in Analytic GRIDs
Chart and report allow a user to select a product in the hierarchy. Chart shows
monthly sales and also sales % of parent. Grid at bottom shows entire parent
ancestry.
7/17/2015
13 tips for MDX in OLAP apps
38
10 - Using MDX in Analytic GRIDs
•
•
•
•
•
•
Product Siblings report requires
custom MDX
Standard designer doesn’t
support ability to use SIBLINGS
Must go to Query tab, and enter
query
Note use of ASCENDANTS to
grab the user’s product
selection, and show all
hierarchical parents
Note that we must now declare
the “background” parameters
manually
Use <<Parameter>> notatation,
and then drag in the
hierarchy/attribute that will
eventually be used in the filter
dropdowns
–
–
–
•
7/17/2015
13 tips for MDX in OLAP apps
<<ProductSelection>>
<<CustomerGeoSelection>>
<<CalendarYearSelection>>
PPS designer will automatically
insert parameters at the bottom,
when you use the << >> notation
in the MDX code
39
10 - Using MDX in Analytic GRIDs
SELECT
[Dim Date Calendar].[Month Key].[Month Key].ALLMEMBERS
ON COLUMNS,
hierarchize(
union( ascendants( <<ProductParm>> ),
<<ProductParm>>.Siblings ) ) ON ROWS
FROM [Jewel Destiny2008R2]
WHERE ( <<YearFilter>>, <<GeoFilter>>, [Measures].[Product Sales as % Parent] )
7/17/2015
13 tips for MDX in OLAP apps
40
11 – Using Named Sets in PPS
• We can drag named sets into a PPS chart
7/17/2015
13 tips for MDX in OLAP apps
41
12 - Using MDX with Excel
1.
2.
3.
4.
5.
Suppose, when browsing an OLAP cube using Excel, we want to incorporate an “onthe-fly” MDX calculation
We can use OLAP PivotTable Extensions (free utility)
After installing, we can right-click on a PivotTable column and add MDX formulas
http://olappivottableextend.codeplex.com/ (Main description)
http://olappivottableextend.codeplex.com/releases/view/91172 (32-bit and 64-bit)
7/17/2015
13 tips for MDX in OLAP apps
42
12 - Using MDX with Excel
([Measures].[Internet Sales Amount],
PARALLELPERIOD( [Date].[Fiscal].[Fiscal Year], 1 ) ),
format_string = '$#,###.##‘
Note: these formulas only reside in the spreadsheet – they are not written
back to the OLAP database
7/17/2015
13 tips for MDX in OLAP apps
43
13 – Passing MDX from .NET
•
•
•
•
7/17/2015
13 tips for MDX in OLAP apps
You’ll need a reference
to the
System.Data.OleDb
namespace
You can write C# or VB
code inside a .NET
application to create an
OLE DB connection to
the OLAP database
Once you do that, you
can create an instance of
an OLE DB command,
and specify the MDX
query in the
CommandText property
The DataSet dsCube will
hold an array of the
results from the query
44