sql server analysis services and mdx
DESCRIPTION
Presentation to the San Francisco SQL Server User Group on June 11, 2009. Christian Wade of EMC discusses the numerous features in Analysis Services 2005 and 2008 as well as dimension/cube design.TRANSCRIPT
Currency Conversion (and More) in Analysis Services
1© Copyright 2009 EMC Corporation. All rights reserved.
Presenter: Christian Wade, Senior Consultant, EMC C onsulting
San Francisco SQL Server User GroupJune 11, 2009
Mark Ginnebaugh , User Group Leader
Agenda
Introduction
Types of Currency Conversion
Many-to-many dimensions
Time dimensions
2© Copyright 2009 EMC Corporation. All rights reserved.
Time dimensions
Semi-additive measures
Measure expressions
MDX query authoring and optimization
MDX scripting
Introduction
3© Copyright 2009 EMC Corporation. All rights reserved.
Kern River Field – 81,000 barrels per day
Types of Currency Conversion
One-to-Many
Many-to-One
Many-to-Many
4© Copyright 2009 EMC Corporation. All rights reserved.
�= [Many-to-One] + [One-to-Many]
Demo
5© Copyright 2009 EMC Corporation. All rights reserved.
Many-to-many dimensions
6© Copyright 2009 EMC Corporation. All rights reserved.
Many-to-many dimensions
Base Data
7© Copyright 2009 EMC Corporation. All rights reserved.
Base Data
Many-to-many dimensions
Base Data
8© Copyright 2009 EMC Corporation. All rights reserved.
Base Data
Many-to-many dimensions
Default Aggregation
9© Copyright 2009 EMC Corporation. All rights reserved.
Default Aggregation
Many-to-many dimensions
Forced to be additive: Account B 60%, 40% split
10© Copyright 2009 EMC Corporation. All rights reserved.
Forced to be additive: Account B 60%, 40% split
Many-to-many dimensions
Base Data
11© Copyright 2009 EMC Corporation. All rights reserved.
Base Data
Many-to-many dimensions
Apply conversion
12© Copyright 2009 EMC Corporation. All rights reserved.
Apply conversion
Many-to-many dimensions
Apply conversion
13© Copyright 2009 EMC Corporation. All rights reserved.
Apply conversion
Many-to-many dimensions
Doesn’t aggregate on currency at all
14© Copyright 2009 EMC Corporation. All rights reserved.
Doesn’t aggregate on currency at all
Demo
15© Copyright 2009 EMC Corporation. All rights reserved.
Options for applying conversion rate
Build logic into MDX query
Build logic into MDX script
BI Wizard
Measure expressions
16© Copyright 2009 EMC Corporation. All rights reserved.
Measure expressions
Resources
My blog: http://blogs.conchango.com/christianwade/
Mosha Pasumansky’s blog: http://sqlblog.com/blogs/mosha/
Chris Webb’s blog:
17© Copyright 2009 EMC Corporation. All rights reserved.
Chris Webb’s blog: http://cwebbbi.spaces.live.com/