Splitting a "Yearly" Forecast Record out into 12 "Monthly" Forecast Records
-
I have an SSIS package that currently outputs this dataset:
Some records represent Forecast values by month and some by year as you can see by the values in the Fcast_Context field. This table is a precursor to a fact table in my dimensional model and the other fact tables have a by-month grain.
What I need to do is Conditionally split the "Yearly" records out of the main data flow, divide the Forecast fields value by twelve and distribute the results out amongst (12) new "Monthly" records. I then need to adjust the Forecast_For_Date fields' value, for each of the new records, to represent each month of the year.
After this transformation, I imagine a Union All transformation will be in order to integrate the new records back into the main data flow.
Does this sound like a viable plan? What transformation do I use to create new records based on the value of another record?
Thanks in advance for any advice!!!
-
I'd be looking to do all of this at source (for example, in T-SQL) rather than in SSIS, if possible.
But if it must do it all in SSIS, you're probably looking at an asynchronous script component to generate the additional rows.
No need for a conditional split if you do that; just send everything to the script component and handle the differences (between treatment of monthly and annual) in code.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply