Relational vs Dimensional Model

[kalyan_y]

Hi all,
  Considering performance issues, which one is better? relational or dimensional
  In my opinion, if the requirements include drill ups and drill downs then we go for dimensional modelling. But if there is no need for drill ups and drill downs, then which of the relational or dimensional is preferred and y?

[CognosPaul]

Generally I find dimensional data sources almost always easier to deal with. Performance wise dimensional beats relational hands-down when it comes to aggregating data. The only times I use relational are for finding related items that are not aggregated, and even then I can sometimes efficient ways of displaying the data using dimensional.

For example, if I wanted a count of all sales by street in a given city I'd use dimensional. I could then select a street and drill through to a relational report showing the all the customer details for that street.

[kalyan_y]

Thanks Paul...
  But how about the query generated when using a relational data source and dimensional data source.
When relational model is used, in the generated SQL/MDX, there are 2 options available in the drop down(Native/Cognos SQL) and for dimensional model, there is only one option Native MDX.
Which of these SQLs are effecient? How exactly MDX is different from SQL? How to comprehend it if we may need to write a native SQL against a dimensional model in a report?

[CognosPaul]

Hi Kalyan,

The Native and Cognos SQL are generated based on how your model data. The thing to remember is that they are the same query. The Native SQL is simply the query that is run against the database. Cognos SQL is the full query, it contains the native query, and includes any rollups done outside the database. Think of it as a wrapper around the native SQL.

MDX is an entirely different language, running against an entirely different type of database. Trying to use a relational approach on a dimensional source may cause the reports to run significantly slower if at all. I'd strongly recommend familiarizing yourself with the concept, read through as many resources and tutorials as possible.

Start with http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id9956cr_rptstd_dimensional_reporting_styl.html and http://en.wikipedia.org/wiki/Multidimensional_Expressions

[lindero]

Hi Kalyan,

there is an easier way to learn how relational and dimensional data differs to each other, and therefore what the difference is between SQL and MDX. Relational data is used for detailed queries, but you are not able to drill down or up due to the missing information about any hierarchy structure. SQL is used for query relational datasource like MSSQL Server, DB2, Oracle, etc.

Dimensional data are stored in physical or virtual cubes. You must use MDX as query language because you don't have tables and columns but dimensions and objects (members). OLAP data sources have less detailed data than a relational data source has. There is a sentence: "First analyze, then query" means you come from the top by using an OLAP cube and when you reached the most detailed view of the cube, you drill through to a adhoc query to get the most detailed data within your business data.

The is a thing between relational and dimensional model style: DMR. It is a compromise because through Framework Manager you are able to model relational data dimensionally. But be carefull using it. The relational data volume shouldn't be that large due to a cube creation at report runtime.