Archive for November, 2011

SQL Server 2008R2 BI Presenations

November 11, 2011

I recently gave a couple of presentations about the new Business Intelligence features in SQL Server 2008 R2. Here are the presentations and their PowerPoint slides.

Business Intelligence with SQL Server 2008R2 Overview

Garrett Edmondson, SQL 2008 BI MCTP MCITP

Session Level: 100
– Beginner

Audience: All
users of BI

An overview of the major SQL Server BI components and how they integrate into a wide range of BI solutions from the personal to organizational level. The following BI components will be reviewed, with a particular emphasis on the components new to R2: PowerPivot, Master Data Services, Fast Track, and Parallel Data Warehouse

SQL Server 2008 R2 Scalability to HUNDREDS of Terabytes

Garrett Edmondson, SQL 2008 BI MCTP MCITP

Session Level:
200 – Intermediate

Audience:
All users of BI

Learn how SQL Server 2008R2 can scale to HUNDREDS of terabytes for BI solutions. This session will focus on Fast Track Solutions, Reference Architectures, and Parallel Data Warehousing.

I got most my slides from this Tech Ed Session.  I would highly recommend watching as many Tech Ed sessions as you can they are great resources.

Also the Best Practices as presented in the FastTrack Data Warehouse 3.0 Reference are absolutely spectacular! They can be
very valuable even if you are not going to buy a Fast Track Solution.

Advertisements

Dimensional Modeling Financial Data in SSAS pt2

November 3, 2011

new: Financial Data Warehouse video 

Unary Operator and Aggregating over time

In my last post I discussed how to arrange all of the GLs into a Parent Child Hierarchy. I also mentioned that I would address the issue of having the child GLs sum to their parent through addition or subtraction.

Using Unary Operators as an attribute in the GL Account dimension controls the aggregations of child GLs to their parents.

See example below:

Net Sales = Goss Sales – Returns and Adjustments – Discounts

ni-statement

Unary Operator Attribute in the Adventureworks DimAccount dimension:

SELECT
AccountCodeAlternateKey
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[Operator]

FROM [AdventureWorksDW2008R2].[dbo].[DimAccount]
where AccountDescription = ‘Net Sales’
or AccountDescription = ‘Gross Sales’
or AccountDescription = ‘Discounts’
or AccountDescription = ‘Returns and Adjustments’

image

Cube configuration in BIDS:

image

The last step is to create another attribute for controlling how these GLs aggregate across time. The Balance Sheet GLs are semi-additive (meaning like personal bank accounts they have running balances) and the Net Income GLs are fully additive across time. More SSAS Cube configurations are  needed to be able to have different parts of the same GL Parent Child Hierarchy aggregate differently over time.

Configure GL Account dimension to an Account Type then configure the SSAS Database like the following:

image

Keep in mind that you will need to create the ‘Account Type’ attribute to hold the values shown above.

Here is what it looks like in the AdventureWorks DimAccounts table:

SELECT
AccountCodeAlternateKey
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[Operator]
,AccountType

FROM [AdventureWorksDW2008R2].[dbo].[DimAccount]
where AccountDescription = ‘Net Sales’
or AccountDescription = ‘Gross Sales’
or AccountDescription = ‘Discounts’
or AccountDescription = ‘Returns and Adjustments’

image

Now that all the required GL Account  attributes have been created ( unary, ‘Account Type’) and the GLs have been arranged into a Parent Child Hierarchy, how to you manage these important attributes that are not present in the source system? the answer is:

Master Data Management with SQL Master Data Services