Guideline to create Cognos project

[joshtheflame]

Hi Gurus,

I am new to Cognos 8.4 and need to know the step by step kinda guideline to initiate a Cognos Project.

For example.

If we have Oracle 10g database in which I have an application schema and my
company is going to develop 10 to 12 dashboards, 40 to 100 Reports(all kind) and couple of scorecards alongwith the ability to drill down charts upto any level what needs to be done?

I have done the following so far.

1. Created Content Store in SQLServer (I didnt know that i need to change the NLS_DATABASE_PARAMETERS) so installating database again is kinda hectic so to kill the time i did on sqlserver and will do it later on oracle.

2. Went to framework manager and created DataSource, pointed towards my Oracle database, pull all the tables and created a PACKAGE.

3. Finally published

Now I can see and use the data in my report studio BUT i want to do the analysis and want to create cubes for analysis.

so to create a perfect package for the above requirements what I need to do step by step to CREATE and DEPLOY my first project successfully.

Thanks

[kattaviz]

Hi,

Read the FM documentation. You have all the required info in there.Pay attention to "Guidelines for Modeling Metadata".

HTH

[blom0344]

I would opt for following a modelling training. Simply importing your database objects is just a first step. You need a good start to avoid the many 'pitfalls' you may encounter..

[joshtheflame]

I would opt for following a modelling training. Simply importing your database objects is just a first step. You need a good start to avoid the many 'pitfalls' you may encounter..

You are right blom. I am stuck actually as I just started laerning Cognos. I have the following table structure.

LDI Table
---------
LDI_CD
Operator_Name


TermOp Table
---------------
Term_CD
TermOp Name

Summary Table
----------------
Term_CD
LDI_CD
YEARS
MONTHS
DAYSNUM
HOURS
TOTAL_TIME (need Aggregation here)

Actually this is the table I have and I need to create a drill down chart like when I see the dashboard it would show me a first YEARS like 2007, 2008, 2009 alongwith the summary of total time. then upon clicking on any year bar it would change the view and show me all the existing months of the selected year like Jan, Feb, Mar, Apr, May, Jun etc etc...and show summary of total time similarly and further clicking on any month it shows days like 1, 2, 3, 4, 5, 6 , 7..31...and click on any day it would take me to the last level which is hours..1,2,3,4,5,6,7...24 ...through this exercise I want to see the trends of traffic year wise then month wise then day wise and finally hour of particular day.

To achieve this and based on the above table structures what can be done and how do I model the data and where to go in framework manager.

Please help me to gain knowledge with your support and guidance.

Thanks

[blom0344]

I'd say you may need minimal modelling for this set as you one true fact and 2 dimension tables. As already mentioned you need to read some of the basic modelling documentation or follow a training course..

[joshtheflame]

I'd say you may need minimal modelling for this set as you one true fact and 2 dimension tables. As already mentioned you need to read some of the basic modelling documentation or follow a training course..

Can you alteast give me a hint what changes do I need to make? do i need to create a another table like I did with the first two dimension ? SO after that i will have 4 tables all together ? is this what you are suggesting?

[blom0344]

No, your set of 3 tables should be the proper basis for a starschema. Join the dimensions to the fact and create the proper regular and measure dimensions:

Dimensions:

Operator_Name
Termop Name
Time (YEARS,MONTHS,DAYSNUM,HOURS)

Measure:

TOTAL_TIME

[joshtheflame]

blom thank you for your reply.

I came up with my current model like the following.

LDI Table
---------
LDI_CD (PK)
Operator_Name


TermOp Table
---------------
Term_CD (PK)
TermOp Name


Time_Table
-----------
Time_ID (PK)
Years
Months
Days
Hours


Fact_Table
-------------
LDI_CD       (FK)
TERM_CD    (FK)
TIME_ID     (FK)
TOTAL_TIME


What do you think about it? if you think its fine now do i need to go into start->framework manager-> click on namespace->Create Regular dimension and make cubes? or do i need to create query subject first?

[blom0344]

Getting even better. Are you going for Powercubes or do you want a DMR ?

[joshtheflame]

DMR first because to get into power cubes you need time and more digging so I want to go with DMR.

Just guide to implement this with DMR what steps I should follow in sequence.

[blom0344]

1. Create regular dimensions (hierachy for the time dimension)
2. Create measure dimensions (just one = total_time)
3. Create package where you hide the relational part
4. Publish

Search the FM user guide for  'Defining the Dimensional Representation of the Model' which gives plenty information (perhaps too much in your case), but it is a good start

[joshtheflame]

blom thank you so much for your humble guidance. Just let me know if I am going to the right direction.

Things I did
----------------
1. I made sure all the proper primary keys and foreign keys according to my model.

2. Created Regular dimension and came up like

TIME (dimension name)
...TIME_ID
......TIME_ID (ALL)
......TIME_ID
......YEARS
......MONTHS
......DAYS
......HOURS

3. Created Measure Dimension and dragged "Total_Time" field from model objects and dropped inside Measures area

4. Created "Query Subject" and dropped YEARS, MONTHS, DAYS, HOURS AND TOTAL TIME from the TIME DIMENSION into "QUERY ITEMS AND CALCULATIONS"

5. Defined Determinants for granularity example.
Month
------
Key = Months (field)
Attributes
-----------
Time_ID
Years
Day
Hours
Total_Time

Also made two more for Days and Hours and tested the grouped data which is coming perfect.

Now what to do? should I made PACKAGE and publish or something else left :)

[blom0344]

Yes, you obviously need to define joins between query subjects in the relational part of your model. That would be part of the project prior to defining the regular and measure dimensions.

[joshtheflame]

sorry just EDITED my last post please re read :) My tables have proper primary and foreign keys..still i need to define relationship in cognos? my project viewer shows something like this.

---ICH (Porject name)
.......Oracle (DS) (namespace)
.........IPDATASUM_VIEW (Fact Table name)
...........Time_ID (FK field)
.......... LDI_CD (FK field)
.......... TERM_CD (FK field)
...........TOTAL_TIME (field)

.........LDIOperators (Table)
...........LDI_CD   (field)
...........LDI_Name

.........TERMOperators (Table)
............Term_CD (Field)
............Term name (Field)

........Time_Dim  (Table)
..........Time_ID (PK field)
..........YEARS (field)
......... MONTHS (field)
..........DAYS (field)
..........HOURS (field)


......TIME_DIMENSION
.............TIME_ID
..................TIME_ID (ALL)
..................TIME_ID
........................TIME_ID (field)
.................. YEARS
.......................YEARS
..................MONTHS
.......................MONTHS
..................DAYS
.......................DAYS
..................HOURS
.......................HOURS

........FACTS (Measure)
...........total_time

When i select all the above tables and go into Launch Context explorer and detect relationships and click on DETECT RELATIONSHIP dialogue it says

USING THE OPTION SPECIFIED NO JOINS DETECTED BETWEEN THE QUERY SUBJECTS SELECTED.

[blom0344]

Yep, you either import relationships through detection (make sure this is done properly) or you define them by hand, making sure cardinalities are set right. In the cognos the relationships should be visible as seperate objects. (Nothing done implicitly)