MDX and Transformer

[ian.hawksey]

Hi Guys

New user here  :)

I am using 8.3 Framework Mgr, Transformer, Analysis Studio, Report Studio, over Oracle.
I have a database with c10 million rows, essentially date based. I need to fill in the gaps between records, by essentially adding 'dummy' records.
For example, I have records for Week 1 and Week 4. I want to be able to 'add' records for weeks 2 & 3 and calculate/display a cumulative qty from week 1 thru to week 4.
I don't want to do this directly in the database as it adds an overhead of 5 times more records.

My current thinking is to add a calculated measure in Transformer using some kind of MDX functionality.

Is this possible?
Am I thinking along the right lines?
Any suggestions?

Thanks

Ian

[MFGF]

Hi Guys

New user here  :)

I am using 8.3 Framework Mgr, Transformer, Analysis Studio, Report Studio, over Oracle.
I have a database with c10 million rows, essentially date based. I need to fill in the gaps between records, by essentially adding 'dummy' records.
For example, I have records for Week 1 and Week 4. I want to be able to 'add' records for weeks 2 & 3 and calculate/display a cumulative qty from week 1 thru to week 4.
I don't want to do this directly in the database as it adds an overhead of 5 times more records.

My current thinking is to add a calculated measure in Transformer using some kind of MDX functionality.

Is this possible?
Am I thinking along the right lines?
Any suggestions?

Thanks

Ian

Hi Ian!  I haven't seen you for ages - how are you?

Not quite sure I follow what you mean with regards to the missing measure values - do you want them as zeroes for Weeks 2 and 3, or do you want to copy Week 1's measures to Weeks 2 and 3, or...?

Filling in the missing weeks could be quite simple if you build an Excel spreadsheet including all the weeks and link this into your FM model - the big question is what do you need in place of the missing measures...

Are you still driving around in that 3 series or have you moved on since then?

Cheers!

MF.

[ian.hawksey]

Is that Mark F? If so, how's life with you? As you say long time no see. If not, who is it ? :)

As for the 3 series, that went to the great scrapheap in the sky, and I'm now driving a more humble Vectra  :(

Anyway, to be more specific, I have essentially the following scenario:

Wk 1 Cumul Qty 1000  Additional Qty 100
Wk 2 Cumul Qty 1100  Additional Qty 0
<record missing for wk 3>
<record missing for wk4>
Wk 5 Cumul qty 1100 Additional Qty 0

I want to be able to provide the users with the numbers for wk 3 and wk 4, so that it would look to the user (via a cube):

Wk 1 Cumul Qty 1000  Additional Qty 100
Wk 2 Cumul Qty 1100  Additional Qty 0
Wk 3 Cumul Qty 1100   Additional Qty 0
Wk 4 Cumul  Qty 1100   Additional Qty 0
Wk 5 Cumul qty 1100 Additional Qty 0

Previously we have manufactured the dummy records for wk 3&4  in the database, but it adds so many records the number of records becomes unmanageable (over 100 million eventually).

Any clearer?
And thanks for the interest !

Cheers

Ian

[MFGF]

Hi Ian,

Don't you recognise my picture on the left?  It was taken by Ro one day in Adlington Court. :)

Back in another Vectra eh?  I seem to remember you had one before the BM.

Ah ok - I see what you want to do with the data.

I guess the approach I'd take would be to knock up a quick Excel spreadsheet with the full list of week numbers in it in a single column.  Add this as a data source in FM and import it into a query subject.  Link this query subject using an outer join to the real week table from Oracle (this will force a row for every week, with nulls for the Cumul and Addit values where no week row exists in Oracle).
You could then add calcs for each week to substitute the nulls.

I don't have access to FM/Transformer right now, but if I get chance I will see if I can knock together a working prototype...

Cheers!

MF.

[ian.hawksey]

Hi
Unfortunately I can't see the picture !

Thanks for replying. I think I understand your idea, and it appears to be a different route to the one I've taken, but you get to the point where I get stuck. Namely "You could then add calcs for each week to substitute the nulls". That's the bit I have been trying to do with MDX, and concluded it can't be done.

I have been trying prevMember and things like that, but I can't get it to 'reference' itself, ie if the cumulative value is missing then go back to the previous cumulative value, and keep doing that until you have found a non-null value. If you have any suggestions, they will be greatly appreciated :)

I appreciate this is difficult to explain & understand via this medium. It's really the kind of thing to be explained using a whiteboard and lots of arrows and crossings out !

Cheers

Ian

[CognosPaul]

I smell a challenge.


Assuming the data appears like this:


Week   |  Value
Wk 1   |    1
Wk 2   |    1
Wk 3   |    2
Wk 4   |  Null
Wk 5   |    5
Wk 6   |    8
Wk 7   |  Null
Wk 8   |   21

and you want the data to appear like:


Week   |  Value  | Prev Value
Wk 1   |     1   | Null
Wk 2   |     1   |    1
Wk 3   |     2   |    1
Wk 4   |  Null   |    2
Wk 5   |     5   |    2
Wk 6   |     8   |    5
Wk 7   |  Null   |    8
Wk 8   |    21   |    8


The PrevValue node should be:

Code Select Expand


total([Cube].[Measures].[Actual Value] within set
tail(filter(

except(periodsToDate([Cube].[Date].[Date].[Date],currentMember([Cube].[Date].[Date])),currentMember([Cube].[Date].[Date]))

,[Cube].[Measures].[Actual Value]>0)))

The first PrevValue will be null of course. For a cumulative rollup get rid of the tail and the except.

This would only work in RS, and doesn't address the underlying issues though.

[ian.hawksey]

Hi PaulM

Thanks for taking an interest in this little conundrum.

The problem here (or at least one of the problems) is that I want the users to be able to use Analysis Studio and see the correct figures, even though they don't actually exist in the source database.

If I've understood you correctly, your suggested solution would only work in Report Studio.

Just to make sure I have explained it, we have the following in the database:

Wk 1 Cumul Qty 1000 
Wk 2 Cumul Qty 1100 
<record missing for wk 3>
<record missing for wk4>
Wk 5 Cumul qty 1100

I want the user to see in Analysis Studio

Wk 1 Cumul Qty 1000 
Wk 2 Cumul Qty 1100 
Wk 3 Cumul Qty 1100   
Wk 4 Cumul Qty 1100   
Wk 5 Cumul qty 1100

ie fill in the missing records for weeks 3 & 4 with the running, cumulative qty. Actually showing the week numbers is not the issue, its carrying on the cumulative qty that's the issue.

Hope that's helped

Cheers

[MFGF]

Hi PaulM

Thanks for taking an interest in this little conundrum.

The problem here (or at least one of the problems) is that I want the users to be able to use Analysis Studio and see the correct figures, even though they don't actually exist in the source database.

If I've understood you correctly, your suggested solution would only work in Report Studio.

Just to make sure I have explained it, we have the following in the database:

Wk 1 Cumul Qty 1000 
Wk 2 Cumul Qty 1100 
<record missing for wk 3>
<record missing for wk4>
Wk 5 Cumul qty 1100

I want the user to see in Analysis Studio

Wk 1 Cumul Qty 1000 
Wk 2 Cumul Qty 1100 
Wk 3 Cumul Qty 1100   
Wk 4 Cumul Qty 1100   
Wk 5 Cumul qty 1100

ie fill in the missing records for weeks 3 & 4 with the running, cumulative qty. Actually showing the week numbers is not the issue, its carrying on the cumulative qty that's the issue.

Hope that's helped

Cheers

Hi Ian,

You could use Paul's suggested report (built in Report Studio) as a data source for your Transformer model.  That way the figures would get built into the cube and would be available for use in Analysis Studio.

The picture thing was a joke, by the way.  My avatar is Beeker, although some say he does bear a passing resemblance...

MF.

[CognosPaul]

Don't use the method I suggested as a data source. That would mean you'd have to build the cube, run the report, then build another cube. Scary...

Let's try to do this via sql.

Let T1 be:
Week   |  Value
Wk 1   |     1
Wk 2   |     1
Wk 3   |     2
Wk 5   |     5
Wk 6   |     8
Wk 8   |    21

Let T2 be a list of every Wk.

Let MissingWeeks be:

Code Select Expand

select Week, 0 as Value from T2 except select distinct Week, 0 as Value from T1

Let T1B be:

Code Select Expand

select Week, Value from T1
union all
select * from (select Week, 0 as Value from T2 except select distinct Week, 0 as Value from T1) as MissingWks

T1B should now look like:
Week   |  Value
Wk 1   |     1
Wk 2   |     1
Wk 3   |     2
Wk 5   |     5
Wk 6   |     8
Wk 8   |    21
Wk 4   |  Null
Wk 7   |  Null



Let Final be:

Code Select Expand

select Week, Value, sum(Value) over(order by Week) as RunningTotal
from
   (select Week, Value from T1
   union all
   select * from (select Week, 0 as Value from T2 except select distinct Week, 0 as Value from T1) as MissingWks) as T1B
order by Week

That should, in theory, give you:
Week   |  Value  | RunningTotal
Wk 1   |     1   |    1
Wk 2   |     1   |    2
Wk 3   |     2   |    4
Wk 4   |  Null   |    4
Wk 5   |     5   |    9
Wk 6   |     8   |   17
Wk 7   |  Null   |   17
Wk 8   |    21   |   38

That may need a little tweaking, especially if you've got more columns. But as a general idea I think that should work.

[MFGF]

Don't use the method I suggested as a data source. That would mean you'd have to build the cube, run the report, then build another cube. Scary...

Not necessarily - a DMR package would do...

MF.

[Lynn]

I'm a little hesitant to respond because Paul and beeker really know their stuff, so I figure I must be missing something. FWIW...

1) you have data stored showing a quantity by date, but with gaps in the dates where no activity occurs
2) you need to show all dates with add'l qty of zero for the missing rows and cumulative value for each
3) you are building a cube and want the add'l qty and cumul qty as measures

If you outer join the fact data to a date table and use an isnull or coalesce to replace the nulls with zero, then would a running-total function by date give you the cumulative figures?

In your FM model you'd have the query subject that takes care of the outer join and the conversion to zero for the quanity on the missing dates. Then a RS report as the source for the cube uses the running-total function to get the correct accumulation.

If I'm missing something then probably the people who say i have a screw loose were right and now it has just disappeared altogether.

Feel free to ignore this if trying to catch me up appears to be a fruitless endeavor :)

[CognosPaul]

Don't mistake being loud for knowing things  ;D

With your scenario you wouldn't even need to publish the package. Simply create the query in FM, and externalize it as an IQD.

At this point it becomes a matter of determining which query is the most efficient. At 10M rows each milisecond per row costs over 2.5 hours.

[Lynn]

Below is from the 8.4 User Guide, so with Cognos 10 IQDs generated from FM might be a thing of the past.


IQDs generated by Framework Manager will continue to be supported in this IBM Cognos 8 release,
but will not be enhanced. Support for IQDs generated by Framework Manager will be deprecated
in the next major release of IBM Cognos 8. IBM Cognos 8 Transformer will continue to support
IQDs generated by Impromptu in the next major release.

[ian.hawksey]

Lynn, PaulM, MFGF

Thank you all for your contributions.

From my understanding of your suggested solutions, they would involve (in one way or another) effectively generating the additional 'missing' rows before being fed into Transformer.

The crux of the problem here (which has got lost a little in 'translation' as my initial explanation was a little vague) is that if we generate all the missing records, the volume of rows goes sky high. The implication to this is our cube-building server runs out of space. This is why I was meddling with the idea of an MDX calc (or somethng else) in the cube which would magically generate the missing number 'on the fly'.

If I have got this wrong, please feel free to put me straight  :)

[Lynn]

Sorry I don't have another suggestion, but not entirely sure you wouldn't run into the same issue trying to deal with it on the transformer side.

Pass the hat to take up a collection for more disk space  ;D

[redmist]

Dont think there is any other solution other than the suggestion by Lynn but if space is an issue you coul d try creating cube groups based on years. Cube groups also could eating up all the space due to the temp file in which case the last option might be to create separate physical cubes based on years which in turn would reduce the cube build time as well as improve the Analysis Studio performance