Monthly Archives: June 2006
Breaking up large dimensions
What Panorama Did Next (Part 72)
Last Night’s BI Event
BI Documenter
VSTS4DB and Analysis Services
Optimising GENERATE() type operations
WITH
SET MYROWS ASGENERATE
(NONEMPTY
([Customer].[Customer Geography].[Full Name].MEMBERS, [Measures].[Internet Sales Amount]),TAIL(
NONEMPTY([Customer].[Customer Geography].CURRENTMEMBER * [Date].[Date].[Date].MEMBERS, [Measures].[Internet Sales Amount])
,1)
)
SELECT
[Measures].[Internet Sales Amount] ON 0,
MYROWS ON 1
FROM
[Adventure Works]
What we’re doing here is finding the last date that each customer bought something. Using the TAIL function within a GENERATE might be the obvious thing to do here, but in fact it isn’t the most efficient way of solving the problem: on my machine, with a warm cache, it runs in 16 seconds whereas the query below which does the same thing only takes 6 seconds:
WITH SET MYROWS AS
FILTER(
NONEMPTY
(
[Customer].[Customer Geography].[Full Name].MEMBERS
* [Date].[Date].[Date].MEMBERS
, [Measures].[Internet Sales Amount])
AS
MYSET,
NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))
)
SELECT [Measures].[Internet Sales Amount] ON 0,
MYROWS ON 1
FROM
[Adventure Works]
What I’m doing differently here is rather than iterating through each Customer finding the set of dates when each Customer bought something and then finding the last one, I’m saying give me a set of tuples containing all Customers and the Dates they bought stuff on and then using a FILTER to go through and find the last Date for each Customer by checking to see if the Customer mentioned in the current tuple is the same as the Customer in the next tuple in the set – if it isn’t, then we’ve got the last Date a Customer bought something. Obviously operations like this within a GENERATE are something to be avoided if you can.
Can I run SQL against an Analysis Services cube?
STORAGE ‘c:\MyCube.cub’
FROM [Adventure Works]
(MEASURE [Adventure Works].[Internet Sales Amount],DIMENSION [Adventure Works].[Geography],DIMENSION [Adventure Works].[Product])
DISTINCT
KEY ( [Adventure Works].[$Product].[End Date],0 )
AS [oduct0_0], NAME ( [Adventure Works].[$Product].[End Date] )
AS [oduct0_1], MemberValue ( [Adventure Works].[$Product].[End Date] )
AS [oduct0_2]
FROM [Adventure Works].[$Product]
AS [Sales0_0], KEY ( [Adventure Works].[$Product].[Product],0 )
AS [oduct1_0]
FROM [Adventure Works].[Internet Sales]
NATURAL JOIN
[Adventure Works].[$Product]
GROUP BY [oduct1_0]
SELECT
AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )AS [Sales0_0], KEY ( [Adventure Works].[$Product].[Product],0 ) AS [oduct1_0], [Adventure Works].[$Product].[Product],[Adventure Works].[$Date].[Calendar Year]
FROM [Adventure Works].[Internet Sales]
NATURAL JOIN
[Adventure Works].[$Product]
NATURAL JOIN
[Adventure Works].[$Date]
WHERE [Adventure Works].[$Product].[Product] = ‘Mountain-100 Black, 48’
AND [Adventure Works].[$Date].[Calendar Year]=’CY 2002′
GROUP BY [oduct1_0], [Adventure Works].[$Date].[Calendar Year]
[Adventure Works].[$Date].[Calendar Year]
FROM [Adventure Works].[Internet Sales]
NATURAL JOIN
[Adventure Works].[$Product]
NATURAL JOIN
[Adventure Works].[$Date]
WHERE [Adventure Works].[$Product].[Product] = ‘Mountain-100 Black, 48’
OR [Adventure Works].[$Date].[Calendar Year]=’CY 2002′
GROUP BY [oduct1_0], [Adventure Works].[$Date].[Calendar Year]
SELECT
COUNT(*)FROM [Adventure Works].[Internet Sales]
WHERE [Adventure Works].[$Product].[Product] = ‘Mountain-100 Black, 48’
AND [Adventure Works].[$Date].[Calendar Year]=’CY 2002′
Project REAL Code and Docs
1. A set of instructions for setting up the environment
2. Guidance on how to explore the implementation
3. A sample relational data warehouse database (a subset of the Project REAL data warehouse)
4. A sample source database (from which we pull incremental updates)
5. SSIS packages that implement the ETL operations
6. An SSAS cube definition and scripts for processing the cube from the sample warehouse
7. Sample SSRS reports
8. Sample data mining models for predicting out-of-stock conditions in stores
9. Sample client views in briefing books for the Proclarity and Panorama BI front-end tools