Good example project, anyone?

[lyonnesse]

I've been struggling with the theory of good design for some time, and while the web is full of snippets I cannot put anything together in a concrete whole.

I have an FM course reference document, but this gives no guidance on the principles of design in FM.  I have built what I thought was a star schema but it is erroring, and frankly I know the design is wrong without being able to fix it.

I've posted questions around various forums but answers I get lead to more questions, and the elapsed time is painful.  A good example of a well designed project would solve all my issues I think. 

For some background I am trying to build a framework from multiple operational relational databases, and I need to put efficiency at the forefront.  Some reporting requires that a table that typically has 5 million rows a year be joined to a table on a different system that has approx 80,000 rows per day.  The former table has some 500 columns, of which the model needs to reflect at least 100, and the latter has around 250, of which the model needs to expose 80 - 100.  I expect to filter this data for the latest 3 months, and I want to be able to build a cube from the resultant package

I don't understand how to split the row of either table into dimensions that connect to a fact.  I am also trying to create calculated facts from subtracting the date on 1 table from the date on the other.

Does anyone know of a sample project that could give me some pointers, or do youhave one you would be willing to share?

[blom0344]

Well, good design is primarily getting correct results with reasonable performance.  ;D Each situation will be different, so no textbook will cover your exact desired solution.

Basically, a starschema is all about one object containing facts (measures) and 1 or more satelites (dimensions) With multiple facts you will need a common dimension to join over. Joining fact objects directly will result in faulty results.

If you are going to store the result in a cube ,then it may not matter to have separate dimension and facttables. One can easily create a cube from one big spreadsheet. The trick is to single out which columns would represent identifiers (dimensional keys), attributes (descriptive data) and facts (measures)

Apart from this aspect, what are your reporting needs, cubes are all about analysis, not really suited for reporting against individual rows of data. There is also a limit on how many dimensions / levels one would want to store, as to many will result in huge storage issues.

Unless most of the columns you want to extract are measures you may be better of with a relational or DMR model where you can generate to reporting data 'on-the-fly'

[cognostechie]

I have had experience creating FM Model to read data from multiple operational databases. I can tell you right now , you will always have performance issues no matter how you fine tune it (pointing the data source to the same instance for multiple schemas etc).

Considering what you mentioned, you are actually trying to use FM as an alternate to Data Warehousing.

Since you said you need to make cubes, no need to build a FM Model. You can write efficient queries that will point to a single table each and then use that in Transformer which automatically associates (equivalent to joins) the data based on common keys at the lowest level. It will work much better than to create a package and use that to build the cube when you are dealing with multiple operational databases.

I would omit mentioning the downsides as that has already been mentioned here about the limitations of the cube for reporting purposes.   

[blom0344]

Cognostechie,

We do not have enough information to ascertain the OP that a cube is the best solution for her/his reporting needs. Furthermore, reading data from multiple databases does not have to be a problem when they are both within the same instance on the same server. If the content manager datasource is the same for both datasources, then query execution may still be largely at the database side.

I do not think one should start writing queries by hand if you can create a package from a FM model and we have no idea about the granularity of the data that needs to be combined.

To put it in other words, the OP will have to give more details to work on..

[lyonnesse]

Thanks for the help guys, but this only emphasises my need for a guideline project.  I've read tons about dimensions and facts but I still can't apply that to my model.  

I broadly understand the theory but as I said before, I cannot apply it using Cognos.  2 years ago I started to model it in SQL Server and it worked perfectly.  I extracted the data into a SQL db, in exactly the same format as the source system.  Then I extracted dimensional data from this layer and built tables of unique values in dimensions.  I used log-id to create facts with foreign keys to the appropriate dimensions and built an SSAS cube from this data.  Sadly the company went in a different direction.  I no longer have the option of setting up an ETL layer to service the reporting function.  How to represent the same model in Framework Manager is a mystery to me

The only example project I have is from the training manual, so the underlying database is perfectly modelled and bears no resemblance to my problem.

So here is my 200 column data source with its unique key - let's call it log-id.  First I need to count all the rows, so I create a fact with a calculated column I call volume and assign the value 1 to it (as per guidelines from Cognos consultant - I originally tried bringing in log-id to to Transformer to count it, but that won't work).  I also need to calculate several more facts representing minutes between 2 events from 2 different columns in the table.  Now I have masses of attributes, such as the hierarchy in the business that owns the row, the products being represented in the row.  These are dimensions I know.  But how to relate them to the fact?  I tried using log-id as the common key but that creates a 1 to 1 relationship, so can't see the point of that.  I have no keys that represent the dimensions, so I just put the actual values into the fact table, so no need to have dimensions.

As for the suggestion that I don't need FM package to create a cube, can I connect Transformer to an Oracle database source direct?  It's not an option in the set-up I'm using.


I am not trying to copy anyone's project verbatim -  it's just that a picture paints a thousand words.  I don't need anyone to analyse how I should tackle the detail.  If anyone has seen any example real-world projects based on a relational database out on the web, anywhere, please let me know.

As regards the other questions.  The 2 big tables are both from Oracle dbs, but not on the same server, or even in the same domain.  The FM model has to allow the construction of Transformer cubes and Report Studio report development.  Some reports will report off the cube and be linked to drill down reports so that viewers can inspect the underlying data.  Some reports will look at one data source, some the other, and a whole batch of them will look at a joined view of data from the 2 tables.  1 fact that is a recurring requirement is the measure of minutes between a date on one table and a date on the other.

Please folks, just a few examples to get me started?

[blom0344]

As for the suggestion that I don't need FM package to create a cube, can I connect Transformer to an Oracle database source direct?  It's not an option in the set-up I'm using.

Yes, why not. Transformer can relate to both Cognos8 model package and an externalized IQD from the same model. Though we tend to build multidimensional models through DMR nowadays, we still use IQD based models to write data to Powercubes.

My 2 cents:

1. Create model with separate facts (multiple datasources)
2. Define calculations within fact
3. Externalize the resulting submodels as IQD's
4. Use IQD's to build Powercube

[lyonnesse]

Can anyone point me to an example project?

[cognostechie]

Blom -

I never said that making cubes was the best solution for him. It was OP who mentioned that as a requirement. I had clearly mentioned that cubes will have it's downsides.

Your theory about having no issues dealing with multiple schemas if on the same instance is good as a theory and Cognos too says that but have you seen that working with an acceptable performance practically? FM does not have problem reading the no. of rows. It's the no. of joins and local processing that makes it slow. In some cases, you can make sure that processing is thrown on the DB but not in all cases when dealing with multiple schemas. We should keep in mind the path we choose to go forward. Once chosen , you are stuck with it and going forward you will see that it is more of a problem than a solution because the users will come up with the logic..'Since it is working, give us this and that too..'. Then what? You can do a small model for now and make it work, make it a full blown model with multiple schemas and you will start cutting your own feet. At that time, the blame will be on you for choosing the wrong design to have started with..

[cognostechie]

lyonnesse -

I have done 3 projects and all of them are working with Operational databases in real life scenario. Two of them are modelled to read data from multiple operational databases (One from two ERP schemas and one from a CRM and another customised schema). I can give you project examples but you would need to understand that the tables in any Operational system would definately have relationships between facts and dimensions for that system to work. Otherwise, how is the Operational system working? In some cases, you have to create surrogate keys or some unique keys which you can do in the DB and bring that into FM.

In one of the projects with two ERP's, I also have a cube that reads Dimesion info, fact info from both ERPs, another dimension info from an Excel file and it works perfectly without any performance issues. Your databases on different domains should not pose a problem for the cube. For the FM Model, definately yes. I would love to see an FM Model reading millions of rows from databases on different domains without performance issues. Blom was talking about the DBs on the same instance but your situation is different.

Depending on how your data is structured, you will have to figure out how to define the relationship between the facts and dimensions, otherwise any kind of project info will not help you. Once you have that, let me know and I can share my FM Model with you. 

[blom0344]

Blom -

I never said that making cubes was the best solution for him. It was OP who mentioned that as a requirement. I had clearly mentioned that cubes will have it's downsides.

Your theory about having no issues dealing with multiple schemas if on the same instance is good as a theory and Cognos too says that but have you seen that working with an acceptable performance practically? FM does not have problem reading the no. of rows. It's the no. of joins and local processing that makes it slow. In some cases, you can make sure that processing is thrown on the DB but not in all cases when dealing with multiple schemas. We should keep in mind the path we choose to go forward. Once chosen , you are stuck with it and going forward you will see that it is more of a problem than a solution because the users will come up with the logic..'Since it is working, give us this and that too..'. Then what? You can do a small model for now and make it work, make it a full blown model with multiple schemas and you will start cutting your own feet. At that time, the blame will be on you for choosing the wrong design to have started with..

Don't take this personally. When I put in the argument about whether or not the cube would be the best solution, I was not informed yet that the OP had realized this in an earlier version with Microsoft BI cubes. I was simply trying to make the point that we needed more detailed info.

Obviously, we tend to be influenced by good or bad experiences with a choosen solution. Based on your 3 projects you are biased toward one type of solution.

I would not argue that combining 2 or more operational sources is always a viable option. Performance MAY be an issue.. However, the consumer may prefer sub-optimal performance when he can have near-real time information. Building intermediate cubes will be batch oriented adding lag to the entire reporting process.

It is also not necessary to complete the entire model to get an idea about performance issues. Define part of the model and perform stress-tests. This way we approached our DMR models. The bottleneck was never the database, but always the Cognos server trying to cope with producing the HTML output.

In other words, the proof is in the pudding   ;)

[cognostechie]

Good info here Blom, about that Cognos server trying to produce HTML.

I completely Blom that the consumer may decide to take a little hit in the performance as it is giving them the solution without having to do a DW inbetween and in some cases, a sub-optimal performance is really not bad as long as it's within acceptable limits.

By testing without completing the entire model, what I meant was that the users might come up with a requirement to write a report involving 3 different tables from three different schemas with joins between them. Most people don't do this kind of testing with a prototype and realise the problem later on.

I might be biased or missing something about my conclusions from those projects but I have realised that joining tables across different schemas on the same instance still works within acceptable limits but I could not make it work within limits when the schemas were on different instance. If you know of a way to make that work, let me know. In the case of OP here, he/she has databases on different domains !! If you or somebody could make that work within acceptable time limits, I would seriously encourage that as I would love that solution. I could also get you a lot of projects if this could be done  ;) 

[lyonnesse]

Depending on how your data is structured, you will have to figure out how to define the relationship between the facts and dimensions, otherwise any kind of project info will not help you. Once you have that, let me know and I can share my FM Model with you. 

I do not have a remit to change the existing database or create a new one.
I do know what the relationship is between facts and dimensions, and I posted a summary of the issue earlier.  I explained that I have already represented the data in SQL server, but my company moved away from that solution and I now have to represent it in Cognos, which doesn't work the way SSAS does, and does not have the luxury of a dimensionally modelled database underneath it.  I would be very grateful if you could just share one star schema, preferably representing a single table of data, as I have to do.

Thanks in advance

[cognostechie]

FM does have the luxury of reading data from an already created Dimensinally modelled databse. For SAP BW/BI, it brings in the entire cube with all the heirarchies without having to redo the whole work in FM. There are data sources in FM for this. I don't know about SSAS but there is a seperate data source for Analysis Services.

Star Schema is nothing but joining the dimensions to one fact instead of joining Fact to Fact. That is not the fix to your problem.  I already told you that cubes are one solution for you if you know the relationship between facts and dimensions. It can also give you that calculation for minutes for which you need data from different domains. If you need to know how to make cubes without having to go thru FM, read another post on this forum with the title 'running-count..'.

[lyonnesse]

You are WRONG.  Obviously this forum is one of those that issues brownie points for every topic 'answered'.  On Tuesday I ask for a sample project.  On Friday I still haven't got one.

All you are doing is reading the last point I made and we are going round in circles.  You don't understand my issues.  You haven't read Wednesday's post, or you wouldn't have waffled on about cubes.  Please stop trying to answer questions I haven't asked.

I need a sample project.  I do not need any more 'experts' assuming they know what I need. 

[blom0344]

You are WRONG.  Obviously this forum is one of those that issues brownie points for every topic 'answered'.  On Tuesday I ask for a sample project.  On Friday I still haven't got one.

All you are doing is reading the last point I made and we are going round in circles.  You don't understand my issues.  You haven't read Wednesday's post, or you wouldn't have waffled on about cubes.  Please stop trying to answer questions I haven't asked.

I need a sample project.  I do not need any more 'experts' assuming they know what I need. 

No one is forcing you to post on cognoise.com. You are also being rude with your last post. If you do not appreciate the feedback you get, then behave and react professionaly or turn elsewhere for free sample projects!!

[lyonnesse]

Not rude at all.  Just stating facts  Not after freebies, either. 

No-one has been able to describe how to break up a single row in a relational database into facts and dimensions in FM.  I've made several builds, none of which achieve the desired result.  Seeing a working model, or even some screen scrapes of part of one, would have resolved that.  Instead, all the posts have gone off at a tangent. 

This is one topic that could have been answered easily, if not visibly

[Lynn]

Not rude? Just facts? And where are these brownies and waffles? I haven't gotten any! :)

You should consider engaging an experienced Cognos consultant to work with you. Feel free to bash him or her about at will.

Obviously this forum is one of those that issues brownie points for every topic 'answered'.

You haven't read Wednesday's post, or you wouldn't have waffled on about cubes.

I do not need any more 'experts' assuming they know what I need

[blom0344]

No-one has been able to describe how to break up a single row in a relational database into facts and dimensions in FM. 

Okay, my last contribution to this post , but I'm a 'so-called expert' with no example model to offer (as yet) but my own models:

(1) YOU DO NEED TO ISOLATE YOUR DIMENSIONS INTO NEW OBJECTS CONTAINING UNIQUE ROWS...

You could - for example! -  take a massive spreadsheet as a datasource , import this into FM as a pseudo-table , then define dimensions and facts or define a model-query subject including calculations for a 'classic' IQD.

If you are on Cognos 8.4.1. then I can create a simple demo model including data,structure , IQD and Transformer model or share one of my DMR testmodels.

[cognostechie]

You are WRONG.  Obviously this forum is one of those that issues brownie points for every topic 'answered'.  On Tuesday I ask for a sample project.  On Friday I still haven't got one.

All you are doing is reading the last point I made and we are going round in circles.  You don't understand my issues.  You haven't read Wednesday's post, or you wouldn't have waffled on about cubes.  Please stop trying to answer questions I haven't asked.

I need a sample project.  I do not need any more 'experts' assuming they know what I need. 

I am not wrong on whatever I said about technical points but I was definately wrong in understanding your actual problem. Your problem in phychological rather than technical. By asking for an 'example project' , you are presuming that project would have been modelled according to your scenario and would easily provide you the solution.

You tried different ways and did not succeed, nor do you understand anything about
Cognos but you are still in a position to determine that a sample project would
work like a magic wand to solve your problem   ;D

No-one has been able to describe how to break up a single row in a relational database into facts and dimensions in FM.  I've made several builds, none of which achieve the desired result.

Like Blom said, with a combination of DQS, MQS, determinants (like grouing), you can
break a table into Dimension and Facts. You could also hand write the SQL to break the columns into seperate Query subjects but you will have to bring them in a way to avoid redundancies.

Ofcourse, I am not going to tell you the detailed process now as I am a 'self-appointed' expert and not a real one. Like Lynn said, hire a Consultant, pay him the big bucks or take the course from Cognos
where they teach you how to make a Star Schema grouping in FM. Tell them they are all nuts because what they are teaching is not what you wanted in the first place.