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 is based on 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 using the Data Cube Designer.
- 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 joining with other data.
- Performance can be improved by using the warehouse option which is similar to database caching, or building an in-memory cube.
2.3. Hierarchies and time dimensions
- Hierarchies are used for filtering, sorting, grouping, and setting up drill downs.
- An example is a multi-level 'Product' or 'Geography' hierarchy.
- You can define a hierarchy and its levels manually in Dundas BI based on a data source.
- Dundas BI can also define a hierarchy automatically by analyzing existing relationships between native tables.
- A time dimension is a container for one or more date hierarchies that are based on the same calendar system.
- Dundas BI includes a built-in default time dimension which consists of two hierarchies.
- 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 identifies a set of measures and hierarchies for grouping and visualization purposes.
- To define a metric set, you add measures to it and assign hierarchies to its ROWS, COLUMNS, and SLICERS axis.
- In this way, a metric set is like an analytical or OLAP report.
- You can define your own KPI using a metric set.
- Metric sets also handle other things including states, missing data, and sorting.
- A metric set may be auto-generated by Dundas BI.
2.5. Dashboards, reports, and scorecards
- These are just different types of 'views' in Dundas BI.
- They all display data using DV (data visualization) controls which are connected to metric sets.
- 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 embedded on a dashboard.
3. OLAP sources
In the case of OLAP data sources, the pathway is a bit different because OLAP cubes are already optimized, multidimensional datasets. 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 native OLAP cubes along with the measures and dimensions within each cube. Drag any measure or dimension onto the dashboard canvas and begin visualizing the data right away. In this case, Dundas BI auto-generates a metric set for you.
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