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):||5 August 2018|
|PDF File Size:||10.49 Mb|
|ePub File Size:||19.64 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. 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.
DMR – Dimensionally Modeled Relational Model
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. 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 white area, drag fname query item from customer in Physical View to Express Definition white coghos to the right.
You can do a lot with expressions in Cognos. You can check 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 modellingg in the Physical View namespace is a snowflake, but we want it simpler and faster. This means less joins: Customer cognoa joined to 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 cognoz 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.
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 morelling 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 modellng 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 mpdelling concatenate first and last name to a full name query item? Then click the arrow pointing to the left. 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:.
Creating a DMR model
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 dr can publish the data for your business users to query.
We can jumpstart a new project with the Model Design Accelerator MDA wizard to create a single-fact star-schema model, but it may contain errors that’s mode,ling to catch.
Usually, you would only pick the tables necessary for the query or star schema, but the foodmart-reduced data cogos 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 a detail because the lack of knowledge about the model.
So modelllng recommended to take the manual route. 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 modeling this recipe. The foodmart-reduced data source schema is not the same from the one we have cognis. Also, some tables have a foreign key for a table named district, but it’s not in the data source. You can just delete it in the next step.
The subject queries that were created at the beginning of this recipe are known as data source query subject which is comparable to dmt 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.