designing data model

8/3/2019 Designing Data Model

1/37

Designing Data Model

usingModel Design Accelerator

A Practical Approach

History:

Version Description

Change

Author Publish Date

0.1 Initial Draft Mitesh Agrawal 12-Oct-2011

0.1 Review 01 Amit Sharma 12-Oct -011

Designing Data Model using Model Design Accelerator | http://learncognosreports.wordpress.com 1

This is another document from Cognos Reporting and Framework manager for

Beginners series. This document provides overview of Framework Manager,

Architecture, Creating Data Source Connection and Project using Model Design

Accelerator, Data Model Creation in Framework Manager.


2/37

Table of Contents-

Create Data Sources and Project Using Model Design Accelerator...3

Model Organization in Framework Manager.........................................25

Verify the Relationship ...32

Verify t he Model.34

Verify the Data... ...39



3/37

Create Data Sources and Project Using Model Design Accelerator: The first step in order to create

data model is to setup the data source connection. Before creating Data Sources and Project in

Framework Manager using Model Design Accelerator, you will put the database files(Metedata Files)

in one of the Data Base. Here I put Sample Sales database files in the Microsoft SQL Server 2005.

Follow the below sequences to put database file-

G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Now we will Attach Database Files in Microsoft SQL Server. In the below examples we are using SQL

Server, however you can use other RDBMS.

Steps for attach the database file is given below-

Go to StartAll ProgramsMicrosoft SQL Server 2005SQL Server Management Studio.

Connect to Server, type Login ID=saand Password , the same password you have given

during installation, press connect button.



4/37

Now Right click onDatabaseAttach.

Addthe Database Files here by click Add button.



5/37

After attach database file, we will press OK.

We can view database files have been attached successfully, now press OK.

You can view database, name as samplesales on the screen as given below-



6/37

You can view the samplesales database, tables and columns in the tables on Object Explorer

in left side of window.



7/37

Steps to Create a Project in Framework Manager Using Model Design Accelerator-

Go to StartAll ProgramsIBM Cognos BI Developer EditionIBM Cognos Framework

Manager.

Click on create a new project using Model Design Accelerator of Framework Manager

welcome page.



8/37

In a new project page, give project name and location for creating a new project and

click OK.

Type User ID and Password in the corresponding block for authentication. If you

provided password during the installation, then write password here ,otherwise keep it blankand click OK.



9/37

In the selected language page, select the design language for the project and click

OK.

Select a data source connection, if you have created previously and click next. If you didnt

create data source connection then, create a new data source connectionby click on New

button. In my case here I will create a new data source connection.

New data source wizard page appear, it helps you to create a new data source and click

Next.



10/37

Specify a name and location and click Next. The description and screen tips are optional.

Specify a Type for the connectionof this new data source.



11/37

Now from Type: drop down list will appear,select the Microsoft SQL Server (SQL 2005 Native

Client) database and for Isolation level, click the default object gateway check box and click

Next.

Specify a server name (which is same as full computer name) ,and Data base name.

NOTE- To see the Server Name, follow the steps given below-

Go to Start->My Computer->Properties->Computer Name.



12/37

Now select a signons check boxand type theUser ID= saand Password, then click on Test the

connection.

Click Test, to make up the database connection.



13/37

The Result will be display,If the path specified here is correct and thestatus is succeeded, then

Press Close.

Following screen will be display the database connection, now click Close.



14/37

New Data Source Page will appear and Press Next.

Now click Finish button.



15/37

The New Data Source Wizardsuccessfully created a data source and press Close.

Select the data source for creating metadata model, which we have created and press Next

button.



16/37

Select the check boxes for the object, which we want to use in the Model Design Accelerator

and click continue.

The Model Design Accelerator Page will appear and click Closebutton..

The Model Design Accelerator Page will appear, it contains the Explorer Tree Pane and Model

Accelerator Pane.



17/37

In the Model Accelerator Pane, Right Click the Fact Table Query Subject in the center of the pane

and click Rename. Here I amspecify a name as Fact Revenueto thisFact Table Query Subject.



18/37

In the Explorer Tree Pane, expand Fact Table Query Subject and drag the selected Query Items &

drop it in the Fact Table Query Subject,which is in the center of Model Accelerator Pane.

We can view the selected Query Items will be appear in the Fact Revenue Query Subject ,which

is in the center of Model Accelerator Pane.

Similarly, In the Explorer Tree Pane, expand Dimension Table Query Subject and drag the

selected Query Items & drop it in the any Dimension Table Query Subject , of Model Accelerator

Pane and right click the New Table Query Subject and click Renameand specify a new name.



19/37

We can view the selected Query Items will be appear in the New Query subject ,which appear as

Dim Customer of Model Accelerator Pane and the relationship between Dimension and Fact

Query subject automatically generates.

Similarly, In the Model accelerator Pane, Right Click any New Table Query Subject and click

Renameand specify a new name to this New Query Subject. In my case ,I am specify a name as

Dim_Segment.



20/37

Similarly, From the Explorer Tree Pane, we can expand more Dimension Table Query Subject and

drag the selected Query Items & drop it in the Dimension Table Query Subject, which were you

specify a new name, of Model Accelerator Pane.

[Remember -To add New Query Subject use this icon( ) and to create a relationship use this

icon( )]

The Relationship Editing Mode for: Segment Dialog Box open, as you can view in the below

screen. This dialog box opens because IBM Cognos Framework Manager cant determine the

relationship between the Dim Segment table and Dim Customer table. So we need to establish



21/37

the relationship between tables yourself and Ctrl-click the Dim_SegmentSegment_Id and

Dim_CustomerSegment_Id.

Modify the Relationship window will appear, it shows the One to many relationship between

Segment and Customer Table and click Ok.

Again Click Ok to close the Relationship Editing Mode dialog box.



22/37

Now click Generate Model.



23/37

A Framework Manager model will generate and Model Design Accelerator will close. Do you

want to continue press Yes. The Model Design Accelerator creates a model based on your

selections. When complete, the model will appear in the IBM Cognos Framework Manager UI.

Model Organization in Framework Manager

The Model Design Accelerator follows the layered modeling approach and creates a three-

layered model automatically. We can view the three-layered model in the Project Viewer.

The Physical View, contains the data source query subjects as they were when imported from

the data source. The Physical View Layer is shown in figure.



24/37

By default, it will provide you a Query Subjects and Query Items which will appear in the IBM

Cognos Framework Manager UI. After create a relationship, we can view the circular joins

among Facts_Rev,Dim_Product,Facts_Inv and Dim_Market tables. To remove circular joins , we

need to create a Alias Table.

Steps to create an Alias Table-

To create Alias Table Right-click on a Physical TableCreate AliasShortcut. Here I will Create

a Alias table to Dim_Product Table.

We can view the shortcut to Dim_Product Query Subject in the below screen.



25/37

Create Relationship between Query Subjects-

Now we will create a relationship between shortcut to Dim_Product andFactInv Query subject.

The Relationship Definition window will appear. This window will show us relationship between

shortcut to Dim_Product andFactInv Query subjectsandjoin operator (Equi Join)between them

and press Ok.



26/37

We can view the relationship has been created between shortcut to Dim_Product andFactInv

Query subjects. Similarly we can create a more Alias to Query subject.

After creating a relationship between all the Dimension and Fact Query Subjects We can viewthe Physical Data Model ,which will appear in the Physical View of IBM Cognos Framework

Manager UI.



27/37

The Business View, shown in Figure, typically consists of model query subjects that are used

either to remodel the Physical View to meet reporting requirements or to consolidate

information from the Physical View for a cleaner presentation of the metadata to the user.

In my case, Model Design Accelerator will automatically merge the Dim_Segment and

Dim_Customer Query Subjects.After creating a relationship between all the Dimension and Fact

Query Subjects, We can view theBusiness Data Model , which will appear in the Business View

of IBM Cognos Framework Manager UI.



28/37

The Presentation View, shown in Figure, typically contains star schema groupings, which are

logical groupings of fact and related dimension query subject shortcuts.



29/37

Verify the Relationship

To create a relationship between Dimesionand FactQuery subjects , just click the relationship

icon , which will appear on the Tool Menu of Framework Manager. Here we will create a

relationship between Time and Facts Query subjects.

[Remember -To create a relationship use this icon( )]

The Relationship Definition window will appear. This window will show us relationship between

Time and Facts Query subjectsandjoin operator(Equi Join)between them and press Ok.



30/37

We can view that one-to-many relationship has been created in between Time and Fact Query

Subjects. After create a Relationship among all the Dimension and FactQuery Subjects, the data

model will create. Remember that, one-to-many relationship should be in between Dimension

andFact Query Subjects.

Verify the Model

To verify the entire model, right click the root model namespace, in my case called

ModelVerify Selected Objects.



31/37

The Verify Model-Options Dialog Box will appear. Select the verify model options to run and

Click Verify Modelbutton.

Verify Model Results dialog box will appear, No Issue detected during model verification and

Press Close.

We can also verify the objects in the model. To do this we will use Model Advisor Tool.

To run the Model Advisor, use the following steps-



32/37

Right Click the namespace that you want to examine. In my case

Go to Sample Sales Run Model Advisor

The Model Advisor dialog box will appear, by default it will provide us already marked check

boxes, please try to keep it same and click Analyze button.

The Model Advisor results dialog box will appear, we can identify items that fits certain

categories. Now click the icon( appear in the red rectangular box) under the Action column to

view the objects in the context explorer.



33/37

The Context Explorer - Model Advisorwill appear, and we can view the objects in the context

explorer.

Again click the icon (appear in the red rectangular box) under the Action column to view the

objects in the context explorer.



34/37

The Context Explorer - Model Advisorwill appear, and we can view the objects in the context

explorer.

Click Close button.



35/37

Verify the Data

In the Project Explorer, expand the Business View , select Product Name(Prod_Name) from

Product Dimension Query Subject and Profit from the Fact Query Subject.

Right Click any one of the selected items and click Test.



36/37


37/37

designing data model

Documents