Framework Manager

[saisachin]

Hi,

could anybody list out advantages/disadvantages of Dimensional over relational models.

Thanks,
kishore

[Arpitagrawal9]

DMR (Dimensionally Modeled Relational) is only required for Analysis Studio. A Relational model will always perform better but if you require drill down and other features of OLAP then that is the solution.

Relational Models are the direct model represented in the database.  They allow for detail reporting and use SQL queries to retrieve the data.
Dimensional Models are either DMR models added  onto a Relational model or more frequently an OLAP data source.  They allow for summary reporting with  hierachies,drill down and unbalanced hierarchies.  They use MDX queries to data the retrieve retrieve the data .

Relational
In this view the modeller will create a model query subject for every data source query subject in the Data Foundation View that is not yet handled by a model query subject. A model query subject is also the best place to use macro functions and parameter maps to handle multilingual tables, containing a field for every language.

Model query subjects can also be used to override settings such as relations at the Data Foundation level. By using the calculations in Model Query Subjects, modellers can refrain from entering freehand SQL in data source query subjects, which should be avoided at all times for maintenance purposes.

For all query subjects where no model query subject was yet defined, a model query subject has to be defined in the Consolidation Views. For those model query subjects in the Data Foundation View, a shortcut will be defined to the Consolidation View. The relational view should only contain model query subjects or shortcuts to model query subjects created in the Data Foundation view.

DMR

Dimensionally Modelled Relational models are virtual OLAP cubes. The data is presented in an OLAP-style, but is not physically stored on the server. Instead at every user request, a query is executed. This style of modelling is used when you want to enable analysis, using drill up / drill down in Analysis Studio.

However, OLAP cubes and a relational star schema model will yield better performance due to the complex nature of a DMR model.

DMR models are made up out of regular dimensions (dimensions) and measure dimensions (facts). A regular dimension consists of one or more defined hierarchies containing levels, keys, captions and attributes. Level information is used to roll up the measures. Each level should have the key and caption defined. If there is a Unique key, the bottom level should be marked Unique, otherwise, the combination of all upper levels is used to identify a member. If the star schema is modelled in its final form, regular dimensions can be quickly generated by using Merge in New Regular Dimension on the relational view of the Consolidation view.

If a regular dimension is based on a query subject that has determinants specified, it is recommended that one level corresponds to each determinant and that the order of levels is equal to the order of determinants. Create a determinant for every level needed. Multiple hierarchies can be specified, but you cannot use them together in a single report query. If this would be a necessity, create a regular dimension for every hierarchy.

A measure dimension is a logical grouping of facts which enables OLAP-styled querying of a relational database. Measure dimensions and regular dimensions are related through scope relations; however the underlying query subject joins remain in use. A scope relation will specify what levels of a dimension are 'in scope' for a certain measure. A scope relation is mandatory and will be created automatically using the underlying query subject joins


Hope this helps

Regards,
Arpit Agrawal