The business requirement was: “We would like to see the number of Open Items that we had in each month for the YTD”.
The SAP aware people, they will know that an open items is a line item that has been posted but not cleared, thus remaining opened for a period of time. In no SAP terms we can just thing that we have a row in the database like:
What business users want is to know per each month how many opened items were in each month. An item is considered open if its clear date is blank (like item 5) or later that the month evaluated, being the month evaluated in its very last day, and, obviously, the open date is before the end of the month being evaluated. Taking the table above, we should have the following results:
|# Open Items||2||2||1|
– Being items 1 and 3 for January, 1 and 4 for February and 5 for March –
As you can see this report is quite different from a “normal” BI report that tells you how many open items we have now.
These kind of reports are called Periodic Snapshots. In Periodic Snapshots the data is evaluated fully per each month that appears in the report. Is like travelling in time and making a picture of the data at the end of each month and later put the results together.
There are other types of snapshot reports called Accumulative Snapshots, but I will not cover them in this post as we will focus only in this Periodic Snapshot case.
The fact table needs to contain the ItemID for each open item in a certain date. In my particular case I need also to tell how many of the open items are “Old” or “Young”. Old items are the ones that have been open for more than 90 days.
My fact table and the dimension tables look like:
In my case the OpenItemID is not important but the AccountID is. Open items are classified by Year (YearID) and Month (PeriodID). If the item is Old gets a 1 in the Old column and a 0 in the Young column and vice versa.
With this structure, an individual item will appear more than once in the table but with different YearID and/or PeriodID. Actually an individual item will appear in each snapshot (YearID+PeriodID combo) as long as it remains open.
Once we have clear how the fact table looks like and how the items will be recorded, we are ready to load the fact table using SSIS.
As data needs to be evaluated “every month” for the period requested (in my case YTD but we will do for 2010 and 2011) it seems natural to have a loop that starts on the 1st of January 2010 and goes until today doing one loop for each month. Inside the loop we will have a query to the data source to get all the open items for that specific month and load them into the fact table.
SSIS has 2 looping controls: For Loop Container, Foreach Loop Container. In this case we will take the For Loop Container.
This control loops based on the EvalExpression expression. As long as this expression remains true the container will keep looping. I have configured mine as follows:
To understand this configuration we need to know that I have created a package variable called InitSnapshotDate of type DateTime. This variable will hold the initial date that we want the loop to start. In my case I configured it to 1/1/2010.
The EvalExperssion calculates the difference in Months between the InitSnapshotDate and today (by GETDATE() function). While the difference is greater than 0 the container will keep looping.
After each loop the AssignExpression expression will be executed. In this case I use the DATEADD function to add 1 month to the InitSnapshotDate value and assign the new value back to InitSnapshotDate.
Inside the For Loop Container you just need to add a normal Data Flow Task with a query to get the open items based on the InitSnapshotDate value passed as a parameter.
Finally this SSIS package is scheduled to run every 1st of the month by a SQL Server Job.
I hope this is clear how to create a Periodic Snapshot fact table and how to feed it with SSIS. Please drop me an email if you have any doubt.