DataWarehouse

hi!

I have a task: create a datawarehouse in Cognos 8 BI.
I’m just a beginner in Cognos… So, can anybody says a basic steps for it? (we use Microsoft SQL Server 2005)
As i read, there is an special tool for creating DW (and DMarts) - DataManager. But we haven’t this tool in our distributiv files. So, can i create DW without DataManager? And how if it possible?
Should i create a database for DW by hand or Cognos can do itself based on created FM-model?

I do following:

  1. Create a new DB in MSSQL2005 and a new connection to it.
  2. Select this DB as DataSource in new FrameworkManager model.
  3. Create a dimentions and measures.
  4. Publish package and open it in MetricStudio.
    But i don’t understand how to use created dimentions and measures in Metric Studio.
    So, when i want to see history of metrics in MetricStudio, i see that metric is incomplete.
    When i try to load data automatically in Staging Tables i have an error: DIS-ERR 3115, DIS-RUN 3259,DIS-RUN 3289: “there is no files in catalog to load”… What kind of files should be in default catalog (CMM) and how can i create it?

p.s. Do not be surprised if the questions seem stupid to you … I’m just a beginner) and anyway sorry for my english =)

i’ll be waiting any reply,
gratefully
Hellenn

hi Ellenn,

i see a few things mixed together. :-\

  1. datamanager is and ETL tool to optimize and transform your (source/reporting) database to a new structure like star schema’s which perform (generally) much better than normal relation databases.
  2. datamanager can be used to load data from one database to the staging tables of the metric store database.
  3. a metricstore databasestructure is created by Cognos. When the metric store structure is created it consists out of staging table and ‘final’ tables. datamanager must only write to the staging tables. When the staging tables are loaded by datamanager, it can be loaded to the ‘final’ tables. Once in the final tables you see the values in the metric studio application.

read the metric administration guides for the staging table formats

http://www.cogknowhow.com/index.php/web-links/43-ibm-cognos-bi

Let us know when you made some progress!

Ok =)
Thank you very much for reply!
I read that staging tables are loads from tab delimited files (.cmo, .cml and other).
But how can i create this kind files without special ETL tool like DataManager (DesicionStream)?
When i create a new metric package several DB-tables appears in my metric store (as i guess it’s tables about metric studio user’s settings) and it’s all.

And one more question… How can i set (define) for each metric corresponding column from a relational database? For exaple, i have an db-table with some data in column ‘quantity’. What should i do to see this data in metric history?

Thank you!
Ellenn

Hi,

When you initialized the metric store database you have a few options to get the data in the staging tables.

  1. Tab-delimited Files

You can use specially formatted tab-delimited files to load data (For SQL database bcp.exe (bulkloadcopy) is used) into the Metric Studio staging tables [Loading Data into Staging Tables] and then into the metric store [Load Data from Staging Tables into the Metric Store] . You can also use these files to export a Metric Studio application from the metric store [Exporting Data from the Metric Store] .

You usually create these tab-delimited files using an extraction, transformation, and loading (ETL) tool that extracts data from an existing system, or by exporting from another Metric Studio application.

You can build the following tab-delimited files:

* Object Stage File (.cmo)
* Metric Type Stage File (.cmm)
* Object Link Stage File (.cml)
* Value Stage File (.cmv)
* Stage Diagram File (.cdo)
* Stage Object Note File (.cmn)
* Stage Custom Parameters File (.cmp)
* Time Periods Stage File (.cal)
* Stage Policy File (.cms)
* Import Source Currency Stage File (.ccm)
* Import Source Reportlet Stage File (.crm)
* Import Source Time Levels Stage File (.ctl)
* Import Source Time Periods Stage File (.ctp)
* Stage Project File (.pro)
* Time Language Text Stage File (.tlt)
* Time Levels Stage File (.lvl)
* Stage Unit File (.unt)
* Cube Query Stage File (.ccq)
* Stage Watchlist File (.cwl)
* Equations (.equ)
* Equation Items (.eqi)

here you can finds more details:
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.inst_cr_winux.8.4.1.doc/inst_cr_winux_id4320UpgradeMM.html

  1. metric designer can do this for you. You create a cube or framework which contains the kpi data you need. You select with a wizard the data and metric designer create and load these files for you.