Understanding the Dundas BI data model
This article outlines the overall data model in Dundas BI.
2. The data model
The following diagram shows the main elements of the Dundas BI data model and how data flows in the system. Although the pathways are a bit different if you're working with relational data versus OLAP data, everything is ultimately modeled though cubes and visualized through metric sets.
The Dundas BI data flow is flexible, as indicated by the background arrows in the figure above and described below. You don't need to create every element in the data flow, and can drag and drop a spreadsheet or database table directly onto a dashboard, for example. It is also modular, giving you the opportunity to reuse and collaborate on any part of the workflow.
Related video: Dundas BI Data Flow
2.1. Data connectors
- A data connector holds the information necessary to connect to a data source.
- The connection information required is different depending on the type of data provider that you select.
- For example, to connect to SQL Server, you generally need to choose an authentication method, specify a server instance, and indicate the database you want.
- Once you've created a data connector, you can go to the Explore window to access native data structures such as tables, views, stored procedures, functions, or OLAP cubes.
- For example, you can drag columns from native tables under your data connector and visualize it right away on your dashboard. Behind the scenes, Dundas BI auto-generates items such as metric sets for you in order to have a complete data flow like the one in the diagram.
2.2. Data cubes (ETL)
- A data cube is a multidimensional model of your data that consists of measures, hierarchies, and metadata such as formatting.
- Works primarily with non-OLAP sources such as relational databases, letting you view your data as a cube without having to use an OLAP system like Analysis Services.
- You can set up an ETL process when editing a data cube by choosing from our wide range of transforms and connecting them together to perform tasks such as data cleansing and combining data.
- The MDX Select transform lets you use an OLAP data source as input for selecting a tabular set of data to join with other data.
- Performance can be improved by using the optional storage types: warehouse to store the cube's output in Dundas BI, or in-memory for faster analytics.
- There is always a cube behind a visualization and its metric set, so a data cube may be auto-generated by Dundas BI if you drag data directly from a data source so that you can start to work with it immediately.
2.3. Hierarchies and time dimensions
- Hierarchies (also called 'dimensions') are used for grouping data into rows and columns, and setting up drill downs.
- Hierarchies can have multiple levels, allowing you to easily drill up/down, expand upper-level items, or change levels.
- You can optionally define a hierarchy and its levels ahead of time in Dundas BI based on a data source, or you can just use a plain column of data such as text or date values as a hierarchy directly.
- A time dimension is a container for one or more date and time hierarchies that are based on the same calendar system, which you can use with any column of date values to group and explore by levels such as Year and Month.
- Dundas BI includes a built-in default time dimension, which you can modify (e.g., to add levels such as Hour if your data includes times of day).
- New time dimensions can also be created based on other calendar types such as your company's fiscal calendar.
2.4. Metric sets
- A metric set selects a set of data from a data source or cube to display, and performs analytic tasks on it such as grouping, sorting, filtering, and formula calculations.
- To define a metric set, you add measures (numerical data), assign hierarchies to its Rows, Columns, and Slicers axes, and change their settings.
- In this way, a metric set is like selecting data in a 'pivot table', a query against an OLAP database, or other applications that use dimensions and measures.
- Metric sets also handle other things specific to the data you're selecting, including states, missing data rules, and formatting. These settings will apply regardless of which visualization you choose to use to display the metric set, and a single metric set can be reused multiple times and visualized in different ways.
- A metric set may be auto-generated by Dundas BI. For example, you can drag and drop data directly onto a dashboard without first creating a metric set from the main menu.
2.5. Dashboards, reports, scorecards, and small multiples
- These are just different types of 'views' in Dundas BI, which display data by combining multiple visualizations connected to metric sets.
- Dashboards are the easiest type of view to create, allowing you to simply place content where you want it, usually for display on a screen.
- Dundas BI reports lay out content into multiple pages with scrolling, ideal for saving to PDF and for printing. (Reports meant for displaying on your screen without much scrolling can usually be created using a dashboard.)
- Reports, scorecards and small multiples have a lot in common: they give you an easy but structured way to create complex views involving repeated visualizations, just by using drag-and-drop operations.
- You can also add a view to another view. For example, a scorecard view can be dragged onto a dashboard and displayed with other content, or you can create a dashboard combining several smaller dashboards together into a personalized view of data.
3. OLAP sources
In the case of OLAP data sources, there is no need for Dundas BI data cubes because OLAP cubes are already optimized, multidimensional data sources. For example, if you have a data connector hooked up to a Microsoft SQL Server Analysis Services (SSAS) database, you can go to the Explore window and see the database's cubes under the data connector, and the measures and dimensions under each cube. Drag any measure or dimension onto a metric set or dashboard canvas, and the metric set will be based directly on the OLAP cube.
3.1. Cube perspective
A cube perspective lets you define a subset of a native OLAP cube. This is an optional item you can create in order to limit the measures and dimensions available to users when creating corresponding metric sets, dashboards, reports, etc.
4. See also
- Video: Dundas BI Data Flow
- Seat types
- Metric set analysis tools
- Automatic joins and hierarchies
- Promoting an auto-generated metric set
- Re-wire the data flow