Thread: PDI - split a value over several periods

1. Junior Member
Join Date
Aug 2012
Posts
12

[Resolved] PDI - split a value over several periods

Hello,

I started using PDI for the development of a draft monitoring energy and water consumption.

I have an excel file type

Name - start date - end date - value

example:

Tom - 01/03/2010 - 20/05/2010 - 10.000 m³

I would like to save this line in a cube and i need to transform each line to :

..
March 2010 _ 3827 m³
April 2010 m³ _3.703
May 2010_2.469 m³
..

I've spent a number of hours on this problem and found no solution ...

I would of course delighted if someone had a track or an idea to submit to me

Thank you to the brave that will work on my problem

----------------------

I post here because the reply don't work :

Waouh !!
Thank you so much Darrell and Michael !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again
Last edited by orangeksr; 09-02-2012 at 10:16 AM.

2. Member
Join Date
Feb 2007
Posts
65
Hmm, this is a nasty case.

-Calculate the period in a number of days.
-Divide the consumption by this value - you get now the daily consumption
-Start a second rowset with a "calendar" on a daily base (generate a row per day), and add a month identifier.
-Combine the streams (f.e. "Join Rows/Cartesian product", set as filter calendar_date >= period_start and calendar_date <= period_end. You get now a row per day with the daily consumption and calendar information.
-use the "group by step" to get a sum of the daily consumption per month.

3. Senior Member
Join Date
Nov 2008
Posts
777
What I would do is this:
1. Take the date range and calculate how many days that is.
2. Calculate the usage per day for that date range.
3. Generate a row in your fact table for each name and for each day in the date range and assign it the daily usage value. Your fact table would look like this:

Fact Table
DateKey NameKey Usage

4. Thus you would reference two dimensions in your fact table: a Date dimension and a Name dimension

Date Dimension
DateKey ActualDate DayOfMonth MonthNum YearNum...

Name Dimension

5. Create a cube schema for the fact table and the dimensions.
6. Write an MDX query with the LastName and Values on the columns and YearNum and MonthNum on the rows.

4. Senior Member
Join Date
Nov 2008
Posts
777
Attached is a sample that does some of what both Michael and I suggested.

Preview the Select Values step and that is basically the fact table I was recommending to build. The advantage of this method is that it is very flexible. Once built, it would be easy to generate a report of usage by year instead of by month. You could also get creative in your date dimension and identify summer days versus winter days and analyze that way. In addition, your name dimension could include Province, City, TypeOfCustomer, etc., and provide analysis in those ways as well. The disadvantage of this method is that it takes time to build and maintain the fact table and dimensions in a cube. Volumes have been written on the subject...data warehouse.

Preview the Sort Rows step and that is the calculation for your statistics as Michael recommended. The advantage of this method is that it is easy to build and run. The disadvantage is that it is not very flexible. You have to change the transformation every time you want a different analysis view.
Last edited by darrell.nelson; 08-29-2012 at 03:49 PM.

5. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative)
and I had to use the "remove" option in select fied.

And Now it is perfect !!

Thank you again Darell and Michael.

6. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again

7. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again

8. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again

9. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again

10. Junior Member
Join Date
Aug 2012
Posts
12
Waouh !!
Thank you so much !
It works perfectly well !
I didn't expect as much.
I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.

And now it is perfect !!

Thank you again

11. Junior Member
Join Date
Aug 2012
Posts
12
Thank You

12. Junior Member
Join Date
Aug 2012
Posts
12

Thank you

Thank you

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•