Modeling without Datawarehousing

[Noel Milton]

Hi All,

I am new to Cognos World and I have the following environment as virtual machine.

Windows 2003 Server (R2)
Cognos 8.4 (full version)
Oracle 10g

I have following table structures

Customer (master)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
CustomerID (Fkey)
SupplierID (Fkey)
Year
MonthNumber
Day
Hour
TotalSales

Now what I want to do is to create some drilldown charts and drilldown sequence will be
YEAR
MonthNumber (Month)
Days(of Month)
Hours (all 24 hours ..24 bars in chart)

And on every drill down it should sum TotalSales and give the total figure.

my question is How can I achieve it using Framework manager without using datawarehousing and make metadata available for self service reporting.

Thanks
Noel

[MFGF]

Hi,

The first step is to import the definitions of these three tables into Framework Manager.  Check that the TotalSales query item has a Usage property of 'Fact' and a RegularAggregate property of 'Total', and make sure that none of the other query items are defined with a Usage property of 'Fact'.

Next you will need to split SalesData into two model query subjects - Time and Sales (possibly needing to manufacture a unique time key via a calculation), and add relationships from Customer (1..1) to Sales (1..n), from Supplier (1..1) to Sales (1..n) and from Time (1..1) to Sales (1..n).  Here you are effectively modelling the data definitions into a virtual star schema within Framework Manager.

Lastly, if you want drill down/up, you will need to create a Regular Dimension based on Customer (single-level), a Regular Dimension based on Supplier (single-level), a Regular Dimension based on Time (levels for Year, Month, Day and Hour) and a Measure Dimension based on Sales (bring in just the TotalSales measure).

Create a reporting package based on these Regular/Measure dimensions.

Regards,

MF.

[Noel Milton]

Hi,

The first step is to import the definitions of these three tables into Framework Manager.  Check that the TotalSales query item has a Usage property of 'Fact' and a RegularAggregate property of 'Total', and make sure that none of the other query items are defined with a Usage property of 'Fact'.

Next you will need to split SalesData into two model query subjects - Time and Sales (possibly needing to manufacture a unique time key via a calculation), and add relationships from Customer (1..1) to Sales (1..n), from Supplier (1..1) to Sales (1..n) and from Time (1..1) to Sales (1..n).  Here you are effectively modelling the data definitions into a virtual star schema within Framework Manager.

Lastly, if you want drill down/up, you will need to create a Regular Dimension based on Customer (single-level), a Regular Dimension based on Supplier (single-level), a Regular Dimension based on Time (levels for Year, Month, Day and Hour) and a Measure Dimension based on Sales (bring in just the TotalSales measure).

Create a reporting package based on these Regular/Measure dimensions.

Regards,

MF.

Thank you so much MFG you mean need to make some structural changes in my oracle tables like this?

Time_Table
TimeID (Primary Key)
Year
MonthNumber
Day
Hour


Customer)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
TimeID (Fkey)
CustomerID (Fkey)
SupplierID (Fkey)
TotalSales

And follow the rest of the instructions?

Thanks

[Noel Milton]

MFG I've done that exactly u told me now i am going to create Regular Dimension.

I am into DIMENSION DEFINITION. New Dimension and have following Available Items.

OracleDS
....
..[-] SalesData
........TimeID
........CustomerID
........SupplierID
........TotalSales

...[-] Customers
........CustomerID
........CustomerName


...[-] Suppliers
........SupplierID
........SupplierName

...[-] Time_Table
........TimeID
........YEARS
........MonthNumber
........Days
........Hours

And its asking to add Hirerchies can you guide me MFG what I need to pick from Available Items?

Thanks for the big help and guide me towards the right direction.

[blom0344]

You must create the hierarchies (1 or more for each dimension) , then you can add levels and drag dataitems into the level. Ideally a Id for _businesskey and a string for _memberCaption

[MFGF]

Thank you so much MFG you mean need to make some structural changes in my oracle tables like this?

Time_Table
TimeID (Primary Key)
Year
MonthNumber
Day
Hour


Customer)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
TimeID (Fkey)
CustomerID (Fkey)
SupplierID (Fkey)
TotalSales

And follow the rest of the instructions?

Thanks

Yes - that looks about right.  Just to clarify - you would not do this in your Oracle database - simply create query subjects in Framework manager that follow these guidelines (and make sure that all the correct relationships are defined between them).

Best regards,

MF.

[Noel Milton]

MFG thanks for the reply but Ive already done this in my Oracle and it took me a lot of time to do it. but it worked...Gosh..Actually you know following was my structure before in Oracle database.

Customer (master)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
CustomerID (Fkey)
SupplierID (Fkey)
Year
MonthNumber
Day
Hour
TotalSales

After your advise I made structural changes in my oracle tables and came like the following.
Time_Table
TimeID (Primary Key)
Year
MonthNumber
Day
Hour


Customer(master)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
TimeID (Fkey)
CustomerID (Fkey)
SupplierID (Fkey)
TotalSales

but your advise is that I shouldnt've done it in Oracle tables and it can be achieved using query subject if I got you right? but can u guide how to do it using query subject to achieve the same?

[MFGF]

MFG thanks for the reply but Ive already done this in my Oracle and it took me a lot of time to do it. but it worked...Gosh..Actually you know following was my structure before in Oracle database.

Customer (master)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
CustomerID (Fkey)
SupplierID (Fkey)
Year
MonthNumber
Day
Hour
TotalSales

After your advise I made structural changes in my oracle tables and came like the following.
Time_Table
TimeID (Primary Key)
Year
MonthNumber
Day
Hour


Customer(master)
CustomerID (Primary Key)
CustomerName

Supplier (master)
SupplierID (Primary Key)
SupplierName

SalesData (transaction)
TimeID (Fkey)
CustomerID (Fkey)
SupplierID (Fkey)
TotalSales

but your advise is that I shouldnt've done it in Oracle tables and it can be achieved using query subject if I got you right? but can u guide how to do it using query subject to achieve the same?

Most folks don't have the luxury of being able to change the structure of the underlying database, so functionality exists in Framework Manager to allow you to emulate these changes, although to be honest, you will get better performance and less modelling required in FM if you make the changes to your database, so what you have done is not wrong!

If you had not been able to modify your database, then you would simply have created the necessary new Query Subjects in Framework Manager (right-click the namespace, point to Create and select Query Subject, then select the Model Query Subject option and drag in the relevant items from the existing query subjects).  Once done, set up relationships as required between the query subjects.

From the sounds of things, none of this is required in your case, since the database is already in the correct structural form after your changes.

Best regards,

MF.

[Noel Milton]

Got your point  but MFG suppose I have my client's structure without TIME DIMENSION then how can I come up with time table using framework manager and query subject like you mentioned without having time related field TIME_ID in table structure....this is what i am unable to understand.
 

[MFGF]

Got your point  but MFG suppose I have my client's structure without TIME DIMENSION then how can I come up with time table using framework manager and query subject like you mentioned without having time related field TIME_ID in table structure....this is what i am unable to understand.
 

There are a couple of approaches covered in this thread - definitely worth reading!

Cheers!

MF.