Adding Date to Excel data

At this point we have just created the table and transferred the basic data, the three columns of data, from the Excel file to the Stage database.

The budget data in the Budget table represents a new set of factual data, i.e. the table is to be regarded as a new fact table – as a new measure group.

Remember that the ultimate goal of the budget data is to be able to compare these to the revenue measure of our current Sales cube. For this to work, the two measure groups need to have at least one common reference – one common dimension. And the only suitable dimension in this case is the Posting Period dimension.

So, the next task for us to make sure that the Budget table and the Posting Period Table can establish a foreign key to primary key relationship.

In the Budget table we don’t have such a key, yet, but it can be fairly easily created from the available information in the table. Go to the SQL Server Management Studio to open a pre-cooked SQL script from the C:\TU folder.

This script will at first add a new colum, TheDate, to the budget table, and then it will create a proper key from the available Year and Month information from the Excel file. To get a proper date, all date keys are set to the first of the month.

Once the script has been executed we can see the result by Select top 100 rows of the Budget table.

Next, we will need to go to the Data Source View of our Analysis Services Project to create the proper relationship between the Budget table and the Posting Period table.

At this point we can go to the Cube editor to add the Budget column as a new measure.

Using the ‘Budget’ column as the source column for the new Budget measure.

At this point, when you try to process the cube with default error configuration, you can expect the processing to fail.

The error you are seeing is due to a few dates (foreign keys) in the Budget table that does not exist as primary keys in the Posting Period table.

To rectify this, we must simply make sure that the dates of the Budget table are presented as input to the Period Table task.

To complete handling of budget data in our SSIS package we should also make sure that the Budget table is truncated properly before being re-populated with potential new data. This is done via another Execute SQL Task.

Also, when we truncate the Budget table we will lose all foreign key information – so they will need to be recreated as well every time the SSIS package is run.

You should pick only the relevant part of the SQL script that we ran previously, put it into yet another Execute SQL Task, and insert both tasks into their correct sequence of the Control Flow.

The final layout of the Control Flow of our SSIS package:

After having run the SSIS package successfully, the new Budget data should be available in the Sales cube. This can be verified by the cube browser.

Amount (Revenue) and Budget together with the Posting Period dimension in the cube browser: