IBM Cognos 10 Framework Manager — Save 50%
A complete, practical guide to using this essential tool for modeling your data for use with IBM Cognos Business Intelligence Reporting with this book and ebook.
In this article by Terry Curran, the author of the book IBM Cognos 10 Framework Manager, we discuss Model Design Accelerator.
Model Design Accelerator is an extension of Framework Manager which has been designed to simplify the design and creation of a single fact relational star schema model. Model Design Accelerator can be used to create a relational star schema model that adheres to proven modeling practice.
This article covers the following topics:
- The Model Design Accelerator user interface
- Starting Model Design Accelerator
- Adding tables
- Adding joins
- Generating Framework Manager models
(For more resources related to this topic, see here.)
By the end of this article you will be able to use Model Design Accelerator to design a new Framework model.
To introduce Model Design Accelerator, we will use a fairly simple schema based on a rental star schema, derived from the MySQL Sakila sample database.This database can be downloaded from http://dev.mysql.com/doc/sakila/en/. It is just one example of a number of possible dimensional models based on this sample database.
The Model Design Accelerator user interface
The user interface of Model Design Accelerator is very simple, consisting of only two panels:
- Explorer Tree: This contains details of the database tables and views from the data source.
- Model Accelerator: This contains a single fact table surrounded by four dimension tables, and is the main work area for the model being designed.
By clicking on the labels (Explorer Tree and Model Accelerator) at the top of the window, it is possible to hide either of these panels, but having both these panels always visible is beneficial.
Starting Model Design Accelerator
Model Design Accelerator is started from the Framework Manager initial screen:
- Select Create a new project using Model Design Accelerator….
This will start the new project creation wizard, which is exactly the same as if you were starting any new project.
- Select the data source to import the database tables into the new model. After importing the database tables, the project creation wizard will display the Model Design Accelerator Introduction screen:
- After reading the instructions, click on the Close button to continue.
- This will then show the Model Design Accelerator workspace.
Adding tables to your workspace
The first step in creating your model with Model Design Accelerator is to add the dimension and fact tables to your model:
- From the Explorer panel,drag-and-drop dim_date ,dim_film ,dim_ customer, and dim_store to the four New Query Subject boxes in the Model Accelerator panel.
- After adding your queries, right-click on the boxes to rename the queries to Rental Date Dim,Film Dim ,Customer Dim, and Store Dim respectively.
- In the Explorer Tree panel,expand the fact_rental table by clicking on the (+) sign besides the name, and from the expanded tree drag-and-drop count_returns,count_rentals, and rental_duration columns to the Fact Query Subject box.
- Rename the Fact Query Subject to Rental Fact.
- Additional dimension queries can be added to the model by clicking on the top-left icon in the Model Accelerator panel, and then by dragging and dropping the required query onto the workplace window.
- Since we have a start_date and an end_date for the rental period, add a second copy of the date_dim table, by clicking on the icon and dragging the table from the Explorer view into the workspace. Also rename this query as Return Date Dim:
If not all query columns are required, it is also possible to expand the dimension tables and drag-and-drop individual columns to the query boxes.
Adding joins to your workspace
After we have added our database table columns to the workspace, we now need to add the relationship joins between the dimension and fact tables. To do this:
- Double-click on the Rental Date Dim table, and this will expand the date_ dim and the fact_rental tables in the workspace window:
- Click on the Enter relationship creation mode link.
- Select the date_key column in the dim_date table, and the rental_date_ key column in the fact_rental table as follows:
- Click on the Create relationship icon:
- Click on OK to create this join.
- Close the Query Subject Diagram by clicking on the (X) symbol in the top-right corner.
- Repeat this procedure for each of the other four tables.
The final model will look like the following screenshot:
Generating Framework Manager model
Once we have completed our model in Model Design Accelerator, we need to create a Framework Manager model:
- Click on the Generate Model button.
- Click on Yes to generate your model.
The Framework Manager model will be generated and will open as follows:
When you generate your model, all of the Model Advisor tests are automatically applied to the resulting model. You should review any issues that have been identified in the Verify Results tab, and decide whether you need to fix them.
When you generate the model only those query items required will be used to create the Framework Manager model. The Physical View tab will contain only those tables required by your star schema model. The Business View tab will contain model query subjects containing only the columns used in your star schema model. The Presentation View tab will only contain shortcuts to the query subjects that exist in the Business View tab.
After generating your model, you can use Framework Manager to improve the model by adding calculations, filters, dimensions, measures, and so on.
Each time you generate a Framework Manager model from your Model Design Accelerator model, a new namespace is created in the current Framework Manager model and any improvements you want to use will also need to be applied to these new namespaces.
From Framework Manager you can return to Model Design Accelerator at any time to continue making changes to your star schema.
To return to the Model Design Accelerator from within Framework Manager:
- From the Tools menu, select Run Model Design Accelerator.
- You may choose to continue with the same model or create a new model.
To make your star schema model available to the report authors, you must first create a package and then publish the package to your Cognos Reporting Server.
In this article, we have looked at Model Design Accelerator. This is a tool that allows a novice modeler, or even an experienced modeler, to create a new Framework Manger model quickly and easily.
Resources for Article:
- Integrating IBM Cognos TM1 with IBM Cognos 8 BI [Article]
- How to Set Up IBM Lotus Domino Server [Article]
- IBM Cognos 10 BI dashboarding components [Article]
eBook Price: $20.99
Book Price: $34.99
About the Author :
Terry Curran gained an interest in computers while studying for his first degree in Biological Chemistry at the University of Kent. He went on to work as a computer operator for a national supermarket company.
After working for several years as a computer operator, he went to Brighton Polytechnic, leaving with a Higher National Diploma in Computer Studies. Upon completion of his studies, he worked as a computer advisor at the City of London Polytechnic, assisting staff and students with their computer problems, and providing support for the various database systems in use across the Polytechnic. After taking voluntary redundancy from this post, he proceeded to the University of Stirling where he gained an MSc in Software Engineering.
After graduating from the University of Stirling, Terry commenced working for a software consultancy company. While working for this company, Terry assisted with the writing of a software package for the publishing industry using Cognos PowerHouse. While working for this company, Terry gained experience in Business Intelligence Reporting tools, being asked to familiarize himself with Cognos Impromptu and PowerPlay in order to promote the use of these tools to clients.
After this company ceased trading, Terry embarked on a career as a freelance computer consultant and contractor, using his experience with Cognos PowerHouse, various computer systems and databases, and Cognos Impromptu and PowerPlay, and later Cognos 8 and Cognos 10.
Terry continues to work as a freelance Cognos Business Intelligence consultant and contractor, making use of his extensive knowledge and experience of IBM Cognos Business Intelligence Reporting. During the past 15 years, Terry has worked for a range of different industries including aviation, pharmaceuticals, insurance, logistics, and manufacturing to name a few.
Terry is currently working for Ultra Electronics Command and Control Systems as a freelance Cognos consultant.
Terry Curran was a technical reviewer for the book IBM Cognos 8 Report Studio Cookbook, Packt Publishing.