April 6, 2016 at 4:28 am
Hi all,
I have 2 dates in my Dimension, Date Invoice and Date Created. If I want my cube to display the Lead Time between it, for example if Date Invoice is 4/6/2016 and Date Created 4/1/2016, Lead time will be 5 (days).
How am I going to achieve this ?
All this time, the calculation is on the viewer, in Excel or in report, to just add a new column then using DateDiff function to these two column. But there is a request to make it in the cube it self.
Kindly advice. Thanks.
April 6, 2016 at 8:37 am
These date values are in your dimension? I'm assuming you are OK with the Lead Time value being a dimension attribute? If so, replace your table in the DSV with a named query and use the DateDiff function to add the Lead Time value into your dimension.
April 6, 2016 at 9:25 am
John Rowan (4/6/2016)
These date values are in your dimension? I'm assuming you are OK with the Lead Time value being a dimension attribute? If so, replace your table in the DSV with a named query and use the DateDiff function to add the Lead Time value into your dimension.
I'd recommend a slightly different approach as first option: Include the measure in your fact table, calculating it in your ETL process. It could even be a calculated column in your relational fact table, but I'd rather do it there than in the cube.
April 6, 2016 at 9:31 am
It really depends on whether the OP needs it as a measure or a dimension member. The original post made it sound like an extension on the dimension. If they had it in the dimension, they could create a lag bucket hierarchy where they could group the dimension members by lag day ranges like 0-5, 6-10, 11-15, 16+. Again, it all depends on if they want to measure it or slice/dice by it.
April 6, 2016 at 9:39 am
John Rowan (4/6/2016)
It really depends on whether the OP needs it as a measure or a dimension member. The original post made it sound like an extension on the dimension. If they had it in the dimension, they could create a lag bucket hierarchy where they could group the dimension members by lag day ranges like 0-5, 6-10, 11-15, 16+. Again, it all depends on if they want to measure it or slice/dice by it.
Sure, and not disagreeing with you.
I would follow the same approach if it was a dimension member, and still add it to the fact table as a measure as well. The only point I'm trying to make is that it is better (imho) to calculate and add it once as opposed to every time the cube is built. Doing it in the dimensional database also means that it is available for all other reporting applications without having to keep on doing the same calculation again.
April 6, 2016 at 9:50 am
I totally agree Martin, in fact, I thought I had put it into my last post. When possible, adding/embellishing the facts/dimension in ETL would be the best approach.
April 6, 2016 at 9:28 pm
Hi all,
Thanks for your kind advice.
I followed the conclusion to add in Fact table then, however I probably doing it the wrong way.
Here is what I did : in my name query of my Fact Table, I added new column "..DateDiff(DD, [tablename].CREATEDDATE, [tablename].INVOICEDATE) as LEADTIME
I thought it will be ok, at first, but if I browse the cube and for example only display those 2 date only, I feel there is something wrong -->
Date create Date invoice Lead time
----------- ------------- ------------
23 Aug 2014 25 Sep 2014 160
23 Sep 2014 26 Sep 2014 6
Understand the value come from the sum of my data Lead time, like in that 2nd row, I bet there are 2 record with the same Date Create and Date Invoice so Lead time is 6, but...... ?
I'm pretty new of cube development, do yo have any reference of this such issue ? and for the ETL thing ? Is
Thanks in advance,
Toni.
April 7, 2016 at 9:13 am
Yes, what you are seeing is the aggregate value...which usually defaults to sum() for any measures. A better aggregate function to use for this measure would probably be average.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply