Transcript Applying MDX Part II

Solving Business Problems
in OLAP Services Using
MDX
- Part II
Amir Netz – Dev Manager & Architect
Ariel Netz – Program Manager
SQL Server OLAP Services
Microsoft Corporation
Still
Yes,
We Are Brothers.
Agenda





Exception floating
Market basket analysis
Working with virtual cubes
Currency conversion
Using UDF in MDX
Knowing MDX
Knowledge
Required
Part I
Functionality
Filtering Sets
Filter(Customers.Members,(Sales,Printers)>50)
{Amir,Ariel,Bill,Corey}
For Each CurrentMember is the set:
Yes
IF
(Amir,Sales,Printer)>50
No
Customer
Sales
(Printer)
Amir
150
Ariel
40
Bill
70
Corey
65
Amir
Filter(Customers.Members,(Sales,Printers)>50)
{Amir,Ariel,Bill,Corey}
For Each CurrentMember is the set:
Yes
IF
(Ariel,Sales,Printer)>50
No
Customer
Sales
(Printer)
Amir
150
Ariel
40
Bill
70
Corey
65
Amir
Filter(Customers.Members,(Sales,Printers)>50)
{Amir,Ariel,Bill,Corey}
For Each CurrentMember is the set:
IF
(Bill,Sales,Printer)>50
No
Yes
Customer
Sales
(Printer)
Amir
150
Ariel
40
Bill
70
Corey
65
Amir
Bill
Filter(Customers.Members,(Sales,Printers)>50)
{Amir,Ariel,Bill,Corey}
For Each CurrentMember is the set:
Yes
IF
(Corey,Sales,Printer)>50
No
Customer
Sales
(Printer)
Amir
150
Ariel
40
Bill
70
Corey
65
Amir
Bill
Corey
Filter(Customers.Members,(Sales,Printers)>50)
Filter({Amir,Ariel,Bill,Corey},(Sales,Printers)>50)
{Amir,Bill,Corey}
Amir Netz:
Demo of filter –
show set before
and after
Customer
Sales
(Printer)
Amir
150
Ariel
40
Bill
70
Corey
65
Exception Floating
For each product group find the the percentage
of the group’s products that had their sales
decreased from the previous time period
Product Group
% Dropped
Food
Drink
Non-Consumable.
Possible answer:
51% of the “Food” products sold
in 1998 did less then in 1997
51%
27%
22%
Business Problem:
For each product group find the the
percentage of the group’s products that had
their sales decreased from the previous time
period
The number of products contained in the group that
had their sales dropped from the previous time period
The number of products contained in the group
Amir Netz:
Phase I:
Demo phase 1
The number of products contained in the current group
Count( products contained in the current group)
Count(Descendants(current group, [Product Name]))
Count(Descendants(
Products.CurrentMember, [Product Name]))
All
Food
Meat
Deli
Meat
S. Dish
Cheese
Drink
Candy
Snacks
Alcoholic
C. Bar
Pop Corn
Soft
Dairy
Phase II
The number of products contained in the current group that
had their sales dropped from the previous time period
Count( products contained in the current group that
had their sales dropped from the previous time period)
Count(Filter( products contained in the current group,
sales < sales of the previous time period)
Count(Filter( Descendants(
Products.CurrentMember, [Product Name]),
sales < sales of the previous time period)
Phase II - Continue
Count(Filter( Descendants(
Products.CurrentMember, [Product Name]),
([Sales],Time.CurrentMember)<
([Sales],Time.CurrentMember.PrevMember)
Amir Netz:
Demo phase 2
Putting It All Together…
Count(Descendants(
Products.CurrentMember, [Product Name]))
/
Count(Filter( Descendants(
Products.CurrentMember, [Product Name]),
([Sales],Time.CurrentMember)<
([Sales],Time.CurrentMember.PrevMember)
Gee…
Amir Netz:
demo
Guided Drill Down
Business Problem:
“There are so many numbers in my cube, can I have the system highlight the
“interesting” numbers?”
Concept:
Have the system guide the user to look at the “interesting” numbers.
Guidance is given in the format of a textual massage.
% Drop
Product Group
Food
Drink
Non
Consumable
Guide
50%
51%
27%
22%
Drill Here
Drill Here
Just like Exception Floating + one “IIF”
IIF(Measures.[% Dropped] >0.5,”Drill Here”,” ”)
Amir Netz:
demo
Distinct Count In The OLAP Space.
Business Problem:
Sales Manager wants to know:
“How many customers are buying Computers? “
“How many active customers do I have?”
Sales
All products
Hardware
Computers
Monitors
Printers
Software
Home
Business
Games
8000
3300
2000
800
500
4700
1500
2500
700
Number of
Customers
200
80
70
60
30
150
100
100
80
First thing to know about Distinct Count :
Distinct Count is not a Count
Business Problem:
:“How many different customers had sales for the
current product/time/geography/promotion…?”
Number of customers that had sales
Amir Netz:
Count(
customers that had sales)
Count( Filter (customers
Demo with bug –
discover the bug
during demo and
fix
,
Sales of current product/time/geo…> 0))
Count( Filter (customers , Sales > 0))
Count(
Count( Filter
Filter (Descendants(
(Customers.[Name].Members, Sales>
Customers.CurrentMember,
Customers.[Name]),
0))
Sales> 0))
Market Basket Analysis
Business Problem:
Sales Manager wants to know:
“It will be very interesting to know how many of my customers bought
both product X and a computer. Maybe a bundle is in place?”
All products
Hardware
Printers
Monitors
Computers
Software
Home
Business
Games
Sales
Number of
Customers
8000
3300
2000
800
500
4700
1500
2500
700
200
80
70
60
30
150
100
100
80
Number Of
Customers Which
Also Bought
Computer
30
30
20
25
30
15
7
10
5
Market Basket Analysis
Printer
WA
Computers
Total WA
Seattle
Total Seattle
190
20
Amir
150
20
Ariel
40
Tacoma Total Tacoma
255
140
Bill
70
80
Corey
65
60
Sue
120
Market Basket Analysis
Printer
WA
Computers
Total WA
Seattle
Total Seattle
190
20
Amir
150
20
Ariel
40
Tacoma Total Tacoma
255
140
Bill
70
80
Corey
65
60
Sue
120
Yes/No
Market Basket Analysis
Printer
WA
Computers
Total WA
Seattle
X
Yes/No
3
1
Total Seattle
190
20
Amir
150
20
Ariel
40
Tacoma Total Tacoma
255
140
2
Bill
70
80
1
Corey
65
60
1
Sue
120
1
0
0
Market Basket Analysis
Concept:
If a customer bought a Printer and a
Computer, “flag” the
customer with the value of “1”, then
sum all the “1” to get a final count.
W
A
Print
er
Compu
ters
Yes/
No
Total
Seattle
190
20
Amir
150
20
Ariel
40
Total
Tacoma
255
140
Bill
70
80
1
Corey
65
60
1
Sue
120
Total WA
Seat
tle
Tac
oma
Count for each product the customers who bought it together
with a Computer)
Amir Netz:
Sum(Customers in the current region,
IIf( Bought both the current product and a Computer, 1,0))
demo
Sum(Descendents(Customers.CurrentMember,
Customer.Name), IIf(Bought current product AND a Computer,1,0))
Sum(Descendents(Customers.CurrentMember,
Customer.Name), IIf((Sales,Products.CurrentMember)>0
AND (Sales,Computers)>0 ,1,0))
1
Market Basket Analysis
W
A
Compu
ters
Yes/
No
Total
Seattle
190
20
Amir
150
20
Ariel
40
Total
Tacoma
255
140
Bill
70
80
1
Corey
65
60
1
Sue
120
Total WA
Seat
tle
Could we do it differently ?
Tac
oma
Yes …
Print
er
Count(Filter
(Descendants(Customers.CurrentMember.Customer.Name),
(Sales>0) AND (Sales,Computers)>0)
))
But …
Not efficient as using the SUM function.
1
Analyzing the Properties
Example for Member Properties:
Gender is a property of a customer.
Education Level Property of an employee.
Store size is a property of a Store.
Amir Netz:
Demo, show
where member
properties are
being defined,
how they look in
the dimension
browser
Analyzing the Properties
Example for Member Properties:
Gender is a property of a customer.
Education Level Property of an employee.
Store size is a property of a Store.
Misconception:
Member properties are only used to provide additional information
on a dimension member.
The truth about member properties:
Member properties are being constantly used in data analysis.
Two ways of using Member Properties in data analysis:
1. Virtual Dimensions
2. MDX
Amir Netz:
Virtual
Dimensions demo
Analyzing the Properties
Business Problem:
What is the sales average per SQFT ?
Sales
Total SQFT in Location X
Amir Netz:
Show demo and
show another
example for
finding the perfect
wife
Sales/
Sum(Descendants(Store.CurrentMember,Store.[Store Name]),
Val(Store.CurrentMember.Properties(“SQFT”)))
Remember: The Store Size (SQFT) is not a Measure.
Virtual Cube
Like a View in a relational database but yet different
Time
Time
Customers
Warehouse
Virtual Cube and
Calculated Members
Common Dimensions
Amir Netz:
Show virtual
cube, different
views, what is
valid and what is
not
Time
Customers
Warehouse
Note: Cubes joined in a Virtual Cube, are not required to have the same number of Dimensions
Currency Conversion
Business Problem:
The sales database is holding sales transactions in US
Dollars. The company needs to show the sales figures in
number of currencies.
Solution:
Two approaches:
1. Present converted sales figures based on the
exchange rate at the time of the sales transaction.
2. Sales figures will be converted to currency X based on today's
exchange rate.
Currency Conversion
Solution:
Two approaches:
1. Present converted sales figures based on the
exchange rate at the time of the sales transaction.
2. Sales figures will be converted to currency X based on today's
exchange rate.
Steps:
1. Create a “Currency” cube - Currency cube holds Currency exchange rate
history.
2. Create Virtual Cube joining the “Currency” cube with the “Sales” cube.
3. Create “Calculated Member” for currency conversion.
Currency Conversion
The “Currency” cube:
Dimensions:
•Time
•Currency
Measures:
•Index - Currency exchange rate for US Dollar
Ariel:
Following this
slide will have a
demo. Demo will
show how to
create the
currency cube,
and the virtual
cube
Currency Conversion
The Calculated Member:
Sales Index
180
C. Member
6
1080
Q1
1998
180
6
Jan
30
1.5
45
Feb
50
2
100
March
100
2.5
250
X
????
Currency Conversion
The Calculated Member:
C. Member
180
6
180
6
395
Jan
30
1.5
45
Feb
50
2
100
March
100
2.5
250
Q1
1998
Sales Index
Currency Conversion
The Calculated Member:
Sales
Index
180
6
180
6
395
Jan
30
1.5
45
Feb
50
2
100
March
100
2.5
250
Q1
1998
C. Member
Sum (each month sales in a given period * Index)
Sum (Descendents(Time.CurrentMember,Month), [Unit Sales] * Index)
Sum (Descendents(Time.CurrentMember,Month),
ValidMeasure([Unit Sales]) *ValidMeasure( Index))
Amir Netz:
Demo.
Currency Conversion
Solution:
Two approaches:
1. Present converted sales figures based on the
exchange rate at the time of the transaction.
2. Sales figures will be converted to currency X based on today's
exchange rate.
Steps:
• Create a “Currency” dimension in the “Sales” cube.
• Create a calculated member for Currency conversion
• Make it smarter by using a UDF.
Amir Netz:
Demo, show with
fix value first then
suggest to go to
the web
UDF - User Defined Function in MDX


UDF allows extension of the MDX
language
Any Automation library can be used
for UDF (VB, VC…)



Just compile your VB class into an
ActiveX DLL and register the DLL in the
OLAP Manager
Make sure to deploy the DLL on the
client side!
VBA and Excel are two libraries that
are automatically registered
Creating a UDF
Functionality:
•Currency Covert ( Input: Currency Name to be converted)
•Based on “Currency Name” go to exchange rate URL.
•Download HTML
•Parse HTML and extract exchange rate for “Currency Name”.
•Return Exchange rate value.
Public Function Currency_Convert(Currency_Name As String) As Double
Dim strHTML As String
Dim URL As String
Dim istart As Integer
Dim iend As Integer
Dim CurrencyRate As Double
Select Case Currency_Name
Case "German Mark"
URL = "http://quote.yahoo.com/m5?a=1&s=USD&t=DEM"
Case "British Pound"
URL = "http://quote.yahoo.com/m5?a=1&s=USD&t=GBP"
Case "Italian Lira"
URL = "http://quote.yahoo.com/m5?a=1&s=USD&t=ITL"
Case "French Franc"
URL = "http://quote.yahoo.com/m5?a=1&s=USD&t=FRF"
End Select
strHTML = Form1.Inet1.OpenURL(URL)
iend = InStr(1, strHTML, "</b></td></tr></table><", vbTextCompare)
istart = InStr(1, strHTML, "</td><td><b>", vbTextCompare)
CurrencyRate = Val(Replace(Mid(strHTML, istart + Len("</td><td><b>"), iend - istart - Len("</td><td><b>")), ",",
"", 1))
Currency_Convert = CurrencyRate