point5pastlightspeed

Just another WordPress.com site

Dimensional vs. Relational

     There seems to be some confusion about the uses and benefits of dimensional versus relational reporting.  A lot of people seem to be on one side of the fence or another.  I myself see significant value in both dependent on the task or process in question.  Let me explain this a little bit better.  When performing ad hoc analysis nothing is better in my mind than dimensional tools.  Often though, complex reports are easier and faster to build when using a relational source.  I often suggest that when considering a new reporting environment both models are utilized.  This allows for flexibility for the end user.

Below is what IBM has to say on the matter:  See also here.

Relational Reporting
Dimensional Reporting
Report type
Lists
Crosstabs
Charts
Maps
Crosstabs
Charts
Maps
Models
Relational models
Dimensionally-modeled relational models
Online Analytical Processing (OLAP) models
Data tree
package
folder
namespace
query subject
query item
measure
level
A customizable source tree, including the following:
package
folder
namespace
fact
measure
dimension
hierarchy
level
level attribute
member
Data items
Data items using relational and common constructs
Data items using dimensional and common constructs
Extended data items
Inserting data
Report Studio groups data automatically by query item. For example, when you add the Product type query item to a list, all product types appear when you run the report.
Report Studio groups data automatically by level. For example, when you add the Product type level to a crosstab, all product types appear when you run the report.
You can also create sets of members from different levels within the same hierarchy. For example, you can create a set of members that includes only Cooking Gear and Lanterns and than add that data to a crosstab.
You can also choose to insert just the member, just the children of the member, or the member and its children.
Summarizing data
Headers and footers in lists
Summary functions
Member summaries
Aggregate within detail 
Member summaries
Aggregate within set
Focusing data
Add a query item and then add a detail or summary filter to view only the data you want to see.
For example, add the Quarter query item to a list and filter by Q3.
Add only the relevant members to an edge of the crosstab or to the context filter.
For example, only add the Q3 member to your report.
Drilling
Drilling through by value
Drilling through by member
Drilling up and down
Page and section breaks
Simple page breaks
Page sets
Sections
Master detail relationships using parameterized filters
Simple page breaks
Page sets
Page layers
Master detail relationships using parameterized edge or slicer expressions
Report Studio authoring mode
Professional authoring mode
Professional authoring model
Express authoring mode
Alternative Studios
Query Studio
Analysis Studio

     When dimensional modeling, the modeler is building relationships between data sets AND parent/child relationships.  This means that a child can only have one parent, but a parent can have multiple children.  Example:  Parent is Northwest, the Children are Oregon, Washington, Idaho, and Vancouver BC.  When using a dimensional model Northwest revenue can be on a crosstab and then a user could drill down on Northwest to see the revenue for Oregon, Washington, Idaho and Vancouver BC.  Dimensional models expand functionality over a relational model.  Also, dimensional model really lend themselves to ad hoc analysis.  Being able to drill up and down a dimension is handy when a  user identifies a problem and needs to uncover the source of said problem.
    A relational model allows for a simple and flexible model.  It is easier to write a report with a relational model and it is easier for an experienced report writer to build a SQL query SQL to achieve very advanced reporting needs.  
    So, what do I prefer to write reports in?  It depends on what I am trying to achieve with the report.  Do I need create something very advanced where I need to do a little remodeling of my own?  If so, I will use a relational model.  Do I need to be able to create drill up and down capabilities?  Then use a dimensional tool. If I am doing strictly ad hoc analysis I would leverage a dimensional model.  If I just need to create a quick simple report I would use a relational model.  Both tools have their uses.
About these ads

Single Post Navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: