sql server analysis services and mdx
Post on 20-Jan-2015
1.773 Views
Preview:
DESCRIPTION
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/
top related