Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data Modeling 1
- Thread starter UcfMike
- Start date
I think that you will need to in order to make fully use of advanced properties of Cognos (multi-fact queries and such)
Modelling dimensionally is probably the one way to avoid inconsistent results.
However, I have the same issue at hand within the next couple of weeks, so I would be most interested in an opinion of the gurus
Ties Blom
Modelling dimensionally is probably the one way to avoid inconsistent results.
However, I have the same issue at hand within the next couple of weeks, so I would be most interested in an opinion of the gurus
Ties Blom
worldofchandran
Technical User
Hi,
I guess u need a datawarehouse in place to work with Framework manager efficiently. Framework manager can never replace the functionality of an ETL tool in building a model for the datawarehouse. It could prove really tedious even for creating a new surrogate key for the model in Framework manager. And if there are data cleansing and standardization issues inthe OLTP database it would not be such a good idea working with the bad data of OLTP system in Framework manager.
Better off creatig enterprise wide datawarehouse model using an ETL and then having the Framework manager to build models that help in creating the required packages(dimensional subsets of the Master model).
Regards,
Chandran
I guess u need a datawarehouse in place to work with Framework manager efficiently. Framework manager can never replace the functionality of an ETL tool in building a model for the datawarehouse. It could prove really tedious even for creating a new surrogate key for the model in Framework manager. And if there are data cleansing and standardization issues inthe OLTP database it would not be such a good idea working with the bad data of OLTP system in Framework manager.
Better off creatig enterprise wide datawarehouse model using an ETL and then having the Framework manager to build models that help in creating the required packages(dimensional subsets of the Master model).
Regards,
Chandran
- Thread starter
- #4
To be honest I'm having trouble wrapping my mind around the concept of dimensional modeling and where FM fits in.
The data warehouse as I understand it is a denormalized representation of the OLTP database consisting of fact tables related to dimensions based on how the business works. The "idea" is simple in theory, in reality there is a reason why data architects command top dollar. If that's true, then the modeling has been done in the data warehouse. So, what is the role of FM in that scenario, other than to provide a conduit to the data for the report writers.
Secondly, how can FM model a relational database dimensionally, if the relational database has never been flattened?
The data warehouse as I understand it is a denormalized representation of the OLTP database consisting of fact tables related to dimensions based on how the business works. The "idea" is simple in theory, in reality there is a reason why data architects command top dollar. If that's true, then the modeling has been done in the data warehouse. So, what is the role of FM in that scenario, other than to provide a conduit to the data for the report writers.
Secondly, how can FM model a relational database dimensionally, if the relational database has never been flattened?
CognosChicago
Technical User
It doesn't really model the relational database dimensionally, it only makes it think it's dimensional. You will need to setup relationships and creat the FM model in a way it to make it think it's dimensional. All the relationships and any tables you need to duplicate will support what appears to dimensional data. Probably confuses more than it helps.
- Thread starter
- #6
I'm sure this has been said before but I'll repeat it so I understand it:
I can't model the database and expect acurate results if I simply recreate the relationships in the OLTP database?
This is something that could be done fairly easily from an Impromptu Catalog, which is the system that I'm currently converting.
I can't model the database and expect acurate results if I simply recreate the relationships in the OLTP database?
This is something that could be done fairly easily from an Impromptu Catalog, which is the system that I'm currently converting.
CognosChicago
Technical User
Mike,
Why can't you? What will cause the results to be incorrect?
Why can't you? What will cause the results to be incorrect?
- Thread starter
- #8
CognosChicago
Technical User
Mike,
I think you can get the right answers out of a OLTP, if you couldn't what use would they be to anyone. I think you might need to create more shortcuts to tables so you can create extra relationships between tables, but it can be done. I've been working with OLTP's for years and reporting off of them successfully for years. There are companies that just aren't willing to put out the money to develop DW's or DM's so you have to work with the OLTP. You just need to verify your information along the way. What this does mean in C8 however is that you can't take full advantage of what the product can do, such as using Analysis Studio against your dimensionally modeled data structure, which by the way you'd have to do to a DM or DW too in FM, just doesn't happen out of the box.
Bottom like Mike, you can get the correct results out of C8, ReportNet, PowerPlay, Impromptu, IWR, etc... with an OLTP, just might take some extra steps to ensure the data is correct.
I think you can get the right answers out of a OLTP, if you couldn't what use would they be to anyone. I think you might need to create more shortcuts to tables so you can create extra relationships between tables, but it can be done. I've been working with OLTP's for years and reporting off of them successfully for years. There are companies that just aren't willing to put out the money to develop DW's or DM's so you have to work with the OLTP. You just need to verify your information along the way. What this does mean in C8 however is that you can't take full advantage of what the product can do, such as using Analysis Studio against your dimensionally modeled data structure, which by the way you'd have to do to a DM or DW too in FM, just doesn't happen out of the box.
Bottom like Mike, you can get the correct results out of C8, ReportNet, PowerPlay, Impromptu, IWR, etc... with an OLTP, just might take some extra steps to ensure the data is correct.
- Thread starter
- #10
Thanks CC. I just wanted to make sure that FM would allow me to model the data that way. The current Catalog is a mess of tables and copied tables so we can create all the proper reports. I wasn't here when it was created, but it appears to be working. I was hoping to do something better, but I'm not sure that's what the client wants.
CognosChicago
Technical User
Not that I would, but you can duplicate what is done in the Impromptu Catalog in FM. If it works and it does what the client wants, then you might just have to do that. You can keep things much cleaner inside FM than Impromptu Admin, but still use the same concepts.
I'm going to have a similiar issue at my client when we migrate their IWR reports to Report Studio reports. Their data isn't dimensional at all, lousy data actually, but I need to get it working in C8 somehow and if that means make it look like Impromptu, then so be it, as long as they get their intended results.
I'm going to have a similiar issue at my client when we migrate their IWR reports to Report Studio reports. Their data isn't dimensional at all, lousy data actually, but I need to get it working in C8 somehow and if that means make it look like Impromptu, then so be it, as long as they get their intended results.
Perhaps it is good to keep in mind that a Framework (likewise catalogue or a BO universe) is only a means to:
1. Generate SQL (for relational sources)
2. Translate database objects to business concepts
If you take the OLTP model and translate it directly in the framework than:
1. Simple queries will probably be resolved without a problem.
2. More complex queries will either lead to incorrect results (overcounting for instance) or there may be more than one path possible for Cognos to resolve the query path.
(what happens then?)
3. Deliberate use of multi-fact queries is simply not possible, cause there is no good model for Cognos to generate the special query construction that it needs.
So, the bottomline will be that if you allow users to select any objects from the model, you may get results that are simply not valid.
In any case you need some sort of presentation to group objects together that form a logical set to query on.
Ties Blom
1. Generate SQL (for relational sources)
2. Translate database objects to business concepts
If you take the OLTP model and translate it directly in the framework than:
1. Simple queries will probably be resolved without a problem.
2. More complex queries will either lead to incorrect results (overcounting for instance) or there may be more than one path possible for Cognos to resolve the query path.
(what happens then?)
3. Deliberate use of multi-fact queries is simply not possible, cause there is no good model for Cognos to generate the special query construction that it needs.
So, the bottomline will be that if you allow users to select any objects from the model, you may get results that are simply not valid.
In any case you need some sort of presentation to group objects together that form a logical set to query on.
Ties Blom
-
1
- #13
I'd completely concur with this. If you just model in FM based on your OLTP structures, the Query Engine may make the wrong assumptions about the data when generating queries within reports. The structures the Query Engine is expecting to find are dimensional - Fact and Dimension tables in a presentation layer. If your data does not currently fit into this scenario, you will need to create new model query subjects in FM to present your data as though it is in these structures (virtual star schemas).
Be aware that this will not fix any data quality issues or cleanse the data in any way, or give any performance improvements - it will simply allow the Query Engine to generate appropriate queries from the data when reports are being created and run.
In an ideal world, all of this dimensional modelling would be done using an ETL tool, and the data would be stored in physical star schema tables in a presentation layer. This would also allow cleansing of the data and handling of slow dimensional changes, and would also give better performance to report off than OLTP structures. In this case the amount of work required in FM would be far less than for OLTP data. In the real world, though, sadly this is not always an option available to BI developers, and more work is required in FM to achieve accurate, predictable results.
MF.
Be aware that this will not fix any data quality issues or cleanse the data in any way, or give any performance improvements - it will simply allow the Query Engine to generate appropriate queries from the data when reports are being created and run.
In an ideal world, all of this dimensional modelling would be done using an ETL tool, and the data would be stored in physical star schema tables in a presentation layer. This would also allow cleansing of the data and handling of slow dimensional changes, and would also give better performance to report off than OLTP structures. In this case the amount of work required in FM would be far less than for OLTP data. In the real world, though, sadly this is not always an option available to BI developers, and more work is required in FM to achieve accurate, predictable results.
MF.
mfgf,
In fact most of my knowledge is based on working with BO universes, so I do recognize a lot within Framework , but with very different technical solutions.
The universe does not allow adding a modelling layer, but one can add contexts to the universe to resolve multiple query-paths and facilitate multi-fact queries.
I am curious how the Cognos product will react on an ambiguous query-path (in the case that no remodelling has taken place) Does it choose a path of it's own. Does it throw an error?
In any case I need to rebuild a couple of universes (very simple models, but OLTP) into Cognos Frameworks and was wondering how to come up with a minimalized solution that would allow rebuilding some basic BO reports
(well, untill we can implement our proper DWH solution)
Ties Blom
In fact most of my knowledge is based on working with BO universes, so I do recognize a lot within Framework , but with very different technical solutions.
The universe does not allow adding a modelling layer, but one can add contexts to the universe to resolve multiple query-paths and facilitate multi-fact queries.
I am curious how the Cognos product will react on an ambiguous query-path (in the case that no remodelling has taken place) Does it choose a path of it's own. Does it throw an error?
In any case I need to rebuild a couple of universes (very simple models, but OLTP) into Cognos Frameworks and was wondering how to come up with a minimalized solution that would allow rebuilding some basic BO reports
(well, untill we can implement our proper DWH solution)
Ties Blom
Hi,
With an ambiguous query path, you will not see an error, but you run the risk that the Query Engine will choose the "wrong" path and introduce a blind spot, resulting in incorrect query results.
With your OLTP data, one very simplistic approach would be to define all your relationships as 1..1 -> 1..1 as this would eliminate the chance of a stitch query being generated. It would not remove blind spots, nor prevent double-counting however, so it's not a hack I would recommend.
To guarantee correct results from your OLTP data, you really need to model as per your FastTrack course, which of course can be quite time-consuming, but I think it's worth it in the end.
Just my opinion, of course
Best regards,
MF.
With an ambiguous query path, you will not see an error, but you run the risk that the Query Engine will choose the "wrong" path and introduce a blind spot, resulting in incorrect query results.
With your OLTP data, one very simplistic approach would be to define all your relationships as 1..1 -> 1..1 as this would eliminate the chance of a stitch query being generated. It would not remove blind spots, nor prevent double-counting however, so it's not a hack I would recommend.
To guarantee correct results from your OLTP data, you really need to model as per your FastTrack course, which of course can be quite time-consuming, but I think it's worth it in the end.
Just my opinion, of course
Best regards,
MF.
- Thread starter
- #16
A lot of great information in this thread.
I am by no means a data warehouse heavyweight, in fact I'm not even a lightweight. I was brought in to create reports, and I was assured the models and packages were already created. They weren't, so I'm getting a crash course in FM and modeling, without the course. I now have 70 Impromptu reports to convert to C8 in 19 weeks, the reports vary in complexity. I'm excited because I think this is a great opportunity.
After reading this thread I think my best option is to develop the reports similar to the way they are in the catalog and impromptu, and on the side, develop a proper modeling system to show the client what could and should be done.
Expect a lot more questions to be posted to this group.
Thanks for your help.
I am by no means a data warehouse heavyweight, in fact I'm not even a lightweight. I was brought in to create reports, and I was assured the models and packages were already created. They weren't, so I'm getting a crash course in FM and modeling, without the course. I now have 70 Impromptu reports to convert to C8 in 19 weeks, the reports vary in complexity. I'm excited because I think this is a great opportunity.
After reading this thread I think my best option is to develop the reports similar to the way they are in the catalog and impromptu, and on the side, develop a proper modeling system to show the client what could and should be done.
Expect a lot more questions to be posted to this group.
Thanks for your help.
Hi There,
I'm trying to make new standard list and crosstab layouts (with new default colours for the footers and summary lines in them).
Main thoughts to-date are that since as soon as we take out data from any template method, we lose the list groupings/footers and its associated formats, it's not the way to go.
I believe we need to a new "Table Style" (in report Studio, you can right-click on the list and apply a table style, which formats it all).
Problem thus far is that we cannot find how to add new table style to the system! Can anyone help pls??
I'm trying to make new standard list and crosstab layouts (with new default colours for the footers and summary lines in them).
Main thoughts to-date are that since as soon as we take out data from any template method, we lose the list groupings/footers and its associated formats, it's not the way to go.
I believe we need to a new "Table Style" (in report Studio, you can right-click on the list and apply a table style, which formats it all).
Problem thus far is that we cannot find how to add new table style to the system! Can anyone help pls??
- Status
Similar threads
- Locked
- Question