Relational and dimensional reporting styles
The Reporting tools and query language are the same for both styles. However, it is important to choose a reporting style to ensure that you are making the most of your data and to avoid mixing dimensional and relational concepts.
How to choose a reporting style
When authoring a report, first choose your preferred reporting style for working with data: relational or dimensional. You can choose a reporting style from your viewpoint:
- If you think about your data as tables and columns, you have a relational viewpoint and should use a relational reporting style.
- If you think about your data as a number of dimensions intersecting at cells, you have a dimensional viewpoint and should use a dimensional reporting style.
Relational reporting style
The relational reporting style consists of lists. You focus the data with filters and summarize with header and footer summaries.
- If your data is purely relational, then only query subjects and query items appear in the Source tab , and you must use the relational reporting style.
- If your data is dimensional, then dimensions appear in the Source tab
, and you can still use a
relational reporting style, but instead of query items (columns) and query subjects (tables), you
use measures, levels, and level properties.
To see an example of relational style reporting with dimensional data, see the Manager Profile sample report in the GO Data Warehouse (analysis) package.
Dimensional reporting style
The dimensional reporting style consists of measures and members from different hierarchies arranged in a crosstab with cell values at the intersections. You focus the data with set expressions that navigate from specific members in the hierarchy and summarize with set summaries.
To see an example of dimensional style reporting with dimensional data, see the GO Balance Sheet as at Dec 31, 2012 sample report in the GO Data Warehouse (analysis) package.
Guidelines for each reporting style
This user guide is divided into relational and dimensional reporting sections so that you can follow the best practices for using this product with the reporting style that you have chosen. The following table outlines the best practices for both reporting styles.
Item |
Relational reporting |
Dimensional reporting |
---|---|---|
Report type |
Lists Crosstabs pivoted from lists Charts created from lists Maps created from scratch or from lists |
Crosstabs Charts created from scratch or from crosstabs Maps created from scratch or from crosstabs |
Package tree |
In the Source tab , click the Options icon , and then click View members tree. If you are using dimensional data, hide members in the source tree by right-clicking the tree, clicking Package Tree Settings, and clearing the check boxes for Members and Members for each level. The view will include the following: package folder namespace query subject query item measure level |
In the Source tab, click the Options icon, and then click View members tree. Ensure that the option Create sets is selected. The view will include the following: package folder measure member named set |
Inserting data |
From the package tree, add query items or levels to the report. Avoid using hierarchies directly in the report. Avoid using named sets. |
From the package tree, add sets of members to the report. You can also choose to insert just the member, just the children of the member, or the member and its children. |
Calculating data |
Add query calculations using only relational and common constructs. Choose only Other expression. |
Add query calculations using only dimensional and common constructs. Use Other expression only for value calculations. Avoid Common functions that are marked with an exclamation mark (!) because they have limited support. |
Summarizing data |
Headers and footers in lists Crosstab member summaries; for example, aggregate within detail |
Member set summaries; for example, aggregate within set |
Focusing data |
Add detail or summary filters 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. Use a set expression such as Topcount or Filter. |
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 |