Understanding the Dundas BI Data Model
If you've followed the previous walkthroughs such as View data with a chart and drill down then you know a bit about data connectors, metric sets, and dashboards. It's a good time then to take a look at 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 visualized through metric sets.
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 a measure column from a native table 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 and hierarchies.
- Works primarily with non-OLAP sources such as relational databases.
- Lets you view your data as a cube without having to use an OLAP system like Analysis Services.
- You can set up an ETL process by adding transform nodes and connecting them together when editing a data cube.
- There are a wide range of transforms available for performing tasks such as data cleansing and joining tables from different databases.
- 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 warehouse option which is similar to database caching, or building an in-memory cube.
- A data cube may be auto-generated by Dundas BI, so that you can immediately work with any data source the same way as you would with a cube.
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.
- Hierarchies may only have one level - a simple column of data in your data source will still be called a 'hierarchy' when used directly in Dundas BI.
- You can define a hierarchy and its levels manually in Dundas BI based on a data source.
- A time dimension is a container for one or more date or time hierarchies that are based on the same calendar system.
- Dundas BI includes a built-in default time dimension.
- You can modify the default time dimension (e.g., extend its date range if your data goes back several years).
- New time dimensions can also be created based on other calendar types such as Fiscal.
2.4. Metric sets
- A metric set selects a set of measures and hierarchies, and how they are grouped, sorted, and filtered.
- To define a metric set, you add measures to it, assign hierarchies to its ROWS, COLUMNS, and SLICERS axis, 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.
- 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, and scorecards
- These are just different types of 'views' in Dundas BI.
- They all display data using visualizations connected to metric sets.
- In Dundas BI, dashboards are the simplest and easiest type of view to create. You can simply place content where you want it.
- What some people call a 'report' should be created as a dashboard in Dundas BI when you lay out all of its contents yourself.
- Dundas BI reports and scorecards 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.
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
- Design overview
- View data with a chart and drill down
- Automatic joins and hierarchies
- Video: Introduction to Data Cubes
- Video: Introduction to Metric Sets
- Video: Understanding Cube Perspectives