Loading Dimensions Using SSIS Packages

This blog is continuation of my previous blog Migrating Data From Hyperion to Analysis Services – Part 1. In this blog we will discuss how to load dimensional data into the Warehouse database. The dimension extract files from Outline Extractor only extract the actual data without any keys. I created dimension tables, added a key column and set the identity Seed and the identity increment to 1.

All dimensions are loaded in a similar way except Time dimension. Here we take an example of Accounts dimension and go thru the details.

Following is a snapshot of Accounts package to load Accounts information into the data warehouse.

The extract does not have any keys so first we truncate all data in table and then load data into the dimension table in the Data Flow Task. Following is a snapshot of data flow.

When data is loaded into the dimension table, we got some duplicate account entries because of some rollup accounts. The duplicate account entries were removed using the following query.

Delete From dimAccount
Where AccountKey In (Select m.MaxKeys From
(
Select distinct a.Account,max(AccKeys.AccountKey) As MaxKeys
From
(
select Account,count(Account) As AccountCount from dimAccount
group by Account
Having count(Account)>1
) a
Inner Join
dimAccount AccKeys On a.Account = AccKeys.Account
group by a.Account
) m
)

After removing duplicate entries, I inserted missing and calculated accounts using INSERT statements. As I auto-generated all the keys, I updated parent account keys using the following query. The parent account keys were used for parent-child hierarchies.

Update [dimAccount]
Set [dimAccount].ParentKey = b.AccountKey
FROM [dbo].[dimAccount]
Inner Join [dbo].[dimAccount] b
On [dimAccount].[ParentAccount] = b.[Account];

Once the ParentKey is updated, I updated Property column of some of the accounts using an Update statement to get correct behavior of the accounts. The Property column behavior was specific to Accounts dimension. The Property column has a plus, minus or a tilde sign. For plus (+) Property values, we used fact values as it is. For accounts with negative (-) property value, fact values needs to be multiplied by -1 to convert it to a negative number.  Accounts with a tilde(~) sign should be ignored.

One important thing to note is that all the accounts were defined as Fact measures in Hyperion but when we extract it, it gets exported as a dimension. Also the parent-child hierarchy does not get exported in any dimension. I manually created the hierarchy by updating ParentKey as mentioned above by comparing accounts.