You want to create a dimensionally modeled relational model from a relational data Data Sources, references to data sources defined in Cognos Manager. Get indepth overview of different types of models i.e., relational model and DMR model in Cognos and their features in detail. Read for More!. Creating a Framework Manager Dimensional Model – DMR. 9- Import the tables/views, Known as Query Subject in Cognos world. Identify.
|Published (Last):||12 June 2004|
|PDF File Size:||19.66 Mb|
|ePub File Size:||6.28 Mb|
|Price:||Free* [*Free Regsitration Required]|
You want to create a dimensionally modeled relational model from a relational data source so you can publish the data for your business users to query. After logging in and selecting a language, the Metadata Wizard will start.
In Select Objectsexpand dboand check Tables. In Generate Relationshipsuncheck Use primary and foreign keys so you can manually create the model. So you just created a project which is a set of components that defines metadata from one or multiple data sources. A Namespace is an object that uniquely identifies stored and organized objects that are found in your model. In Step 1query subjects were imported.
Now we need to create relationships and join the query items with the correct cardinality. Since we imported all tables, creating relationships in Framework Manager would be as simple as copying the database schema.
Best Practices in Modeling IBM Cognos Semantic Layers in Framework Manager | element61
It should validate automatically. Click on Test and you should get the top n rows specified. If nothing shows, something is wrong with your relationship.
You should now have 8 valid relationships. You want to provide a very basic layout of query subjects for business users so they can comfortably create queries, and produce reports or workspaces. Hence the guys at Cognos recommend the: Three Tier Design Physical View holds the query subjects and relationships used to build the model. Query subjects should be unmodified views of the source tables. It’s what you have now.
Business View contains optimized query subjects for use by business users.
Query subjects can be by renamed, moved around, consolidated, merged, etc. Presentation View this view will be presented to the business user. Query subjects are organized in a way that business users can understand. Lets try this with Foodmart-reduced.
Cognos Express Cookbook — DMR – Dimensionally Modeled Relational Model
Move all subject queries into Physical View namespace. Your project should be structured like the following:. A dialog will appear.
Repeat step 5, but drag customer to the white area on the right and then at the bottom right-hand corner, click on Add to add a new Query Item. From the Available Components modelljng area, drag fname query item from customer in Physical View modellinh Express Definition white area to the right. You can do a lot with expressions in Cognos. You can modflling all of their many functions and parameters by clicking on the tabs on the lower left-hand corner, but we will cover that some other time.
Your final expression definition should be:. To test it, click on the blue play button and it should show you the correct results like in the picture below:. We know the schema in the Physical Vognos namespace is a snowflake, but we want it simpler and faster. This means less joins: Customer is joined vmr region, lets merge them.
Now you can remove query items: Rename the newly imported query items and test to see if it works. For organizational purposes, you can put the merged query items in a folder. The scope still remains the same. Before we continue to the other data source query subjects, lets fix the properties for the query items in our Business View. We will focus on one property setting for query item which is Usage.
There are other important properties that we can talk about on another recipe like Regular AggregateFormatPrecisionetc.
Creating a DMR model
With the rule of thumb table above, try to correct the usage type for all query items in Customer. Some of them are correct already. Property settings can be found at the bottom of Framework Manager after selecting a query item. Now that we have created all query subjects for Business Viewlets finish up by creating the Presentation View. In Presentation Viewthere will only be shortcut links that point to the query subjects in Business View.
This allows you to easily modify what business users see without modifying any of the query subjects, and when query subjects are modified in the Business View, changes take effect in the Presentation View as well.
When all are selected, right click on one of them, and choose Create Star Schema Grouping…. Create Star Schema Grouping Dialog will appear. The content is now ready for the business user to use in the form of a star schema grouping. Now you have to define your objects. We only want business users to have access to Presentation View, not Physical or Business View, but we cannot exclude Physical or Business because Presentation depends on it.
Your setting should look like the following image:. Remember when we created the expression to concatenate first and last name to a full name query item? Then click the arrow pointing to the left.
DMR – Dimensionally Modeled Relational Model
The lists should look like the following image and click Finish. You created your package, now you should get a prompt asking if you want to open the Publishing Package Wizard.
The typical place to store packages is in Cognos Content Store. There are two folders in the Content Store you can save your package or pretty much any file type:. As long as you create a snowflake or star schema that has relationships of many-to-one from the inside out, clean things up, denormalize it, and etc, you should be okay.
DMR – Dimensionally Modeled Relational Model The Problem You want to create a dimensionally modeled relational model from a relational data source so you can publish the data for your business users to query. We can jumpstart a new project with the Modellig Design Accelerator MDA wizard to create a single-fact star-schema model, but it may drm errors that’s difficult to catch. Usually, you would only pick the tables necessary for the coghos or star schema, but the foodmart-reduced data source is small enough.
The wizard can create relationships with the joins and cardinality from the data source, but it will still need manual adjustments, and it’s possible for one to miss cogons detail because the lack of knowledge about the model.
So it’s recommended to take the manual movelling. Uniquely identifies means that a namespace has its own scope so two different namespaces can have an object with the same name and there won’t be any conflict because they do not affect one another. You can link one object to an object in another namespace though. It’ll hopefully make more sense by the end of this recipe. The foodmart-reduced data source schema is not the same from the one we have now.
Also, some tables have a foreign key for a table named ocgnos, but it’s not in the data source. You can just delete it in the next step. The subject cognso that were created at the beginning of this recipe are known as data source query subject which is comparable to a table in SQL. In the other hand, model query subjects are made from one or more data source subject queries. This means the query subjects in Physical View should be data source while the query subjects in Business View should be model.