Report or Cube Solution

[silban76]

Hi All,

I have a 10 column table that looks like this:

A B1 B2 B3 C1 C2 C3 D1 D2 D3

with data it will be as follows:

A B1 B2 B3 C1 C2 C3 D1 D2 D3
----------------------------------------
X 0 3 7 1000 1100 1200 0 1 3
Y 1 4 6 1000 1030 1100 0 2 2
Z 1 2 5 1000 1030 1100 1 2 2

I need to design a report or cube using the table that should look like this:

A C1 C2 C3
D1 D2 D3
B1 B2 B3

in data format it should be like this:

1000 1030 1100 1200 ----> VALUES FROM C1 C2 C3
X 0 1 3 ----> VALUES FROM D1 D2 D3
0 3 7 ----> VALUES FROM C1 C2 C3
Y 0 2 2
1 4 6
Z 1 2 2
1 2 5


I am not sure how I can do this. Can someone please suggest a solution. I am using Cognos 7.1

 

[drlex]

silban,
Just so that I understand it, your groupings are data fields A and C1 to C3?
If so, is there a strict relationship between B1, D1 and C1? That is to say, the values in B1 and D1 relate to that in C1?
I would have thought that the easiest way to do it would be a cube, dimensions A and C with measures B & D and the data source being three almost identical iqds, varying only on their assignation of B,C, and D
iqd#1
Table.A as A
Table.B1 as B
Table.C1 as C
Table D1 as D
iqd#2
Table.A as A
Table.B2 as B
Table.C2 as C
Table D2 as D
etc

soi la, soi carré

 

[silban76]

drlex,

You are right when you said
my groupings are data fields A and C1 to C3? and
If so, is there a strict relationship between B1, D1 and C1? That is to say, the values in B1 and D1 relate to that in C1?

The solution you suggested sounds good, however my dilema is that in the example I just used 10 columns(my mistake, should have been more specific) in the original table I have B ranging from B1 to...B96, C from C1 to...C96, and D from D1 to D96.
so all togather 289 fields with A having 4 distinct groups in it.

This table is for restaurant reservations where C corresponds to time slots anywhere from 5 mins to 1hr gap.
D is table reserved in those time slots by restaurant and B is total tables per restaurant...

 

[drlex]

96? [Ponder]

Well, other than beating the fellow who set up this table over the head with a copy of "Strategic and Logical Database design" and leaving early for the weekend, it would appear there's a few ways of tackling this:

1) Get the DBA to set you a View on the table that will give you A,B,C,D and create one iqd against this, or

2) Set up a multi-Unioned report in Impromptu to interrogate your table 96 times for a 4 column output, or

3) Use Impromptu to create one iqd and then a text editor to edit 95 copies to create the 96 similar iqds.

I've had to do option(3) a few times, as my ODBC driver to the DB can't handle arrays, and so I have your issue on tables with month summaries which appear to me as sales@1, sales@2...sales@12.

soi la, soi carré

 

[blom0344]

My advice would be to find out if the table you are trying to report on may be a derived table (with denormalized data from rows to columns)
In other words , there may be a original transactional table that stores the data in a more traditional way. In that case you could standard reporting techniques.

I truly doubt that any sane developer would come up with such a monstrosity for storing transactional data!

Ties Blom

 

[silban76]

drlex and blom0344,

Thank you so much for your suggestions.

However there is nothing much I can do with this table in DB2 as it was vendor designed and we as report developers have very little control on it.

So instead I decided to import the table to our datawarehouse environment and go from there.

Hopefully this will work out.........