Collaborative Workflow Step 1: Data Preparation
Recommended role: Database administrator (DBA)
Purpose: To prepare the underlying data used by a Dundas Dashboard KPI
Data preparation is crucial to ensure that the key performance indicators (KPIs) defined (or in the process of definition) for the dashboard have accurate data. This step is usually
accomplished by a DBA - or anyone with a thorough knowledge of the business data sources - and looks at data connectors and virtual tables.
Dundas Dashboard Data Connectors
The first consideration in this step is gaining connection to an organization's data sources. Dundas Dashboard data connectors facilitate data connection via support for many databases, data caching and auto data schema discovery features:
Support For A Variety Of Databases allows the DBA to connect directly to their data sources through an intuitive user interface. Databases supported include:
- MS SQL Server 2005 & 2008
- Oracle 10g & 11g
- MySQL 5
- Microsoft Access
- CSV (and other flat files)
Data Caching will improve the performance of Dundas Dashboard when retrieving data. Additionally, data connectors can be scheduled to provide routine updates to refresh the data.
Automatic Data Schema Discovery makes it easy to see the data source's data schema so the DBA does not have to open up native database-management tools. Depending on the type of data source, the auto data schema will retrieve tables, views, functions and stored procedures. Data connectors and their associated object definitions can then be saved as part of a Dundas Dashboard project and made accessible to other Dundas Dashboard users.
Data connectors and their associated object definitions can be saved as part of a Dundas Dashboard project - once saved, the data connectors can be made accessible to other Dundas Dashboard users. Now the next task for this step is to define the virtual tables.
Dundas Dashboard Virtual Tables
One or more virtual tables (or virtual structures) must be created for the business analysts to use when designing KPIs. A virtual table enables the DBA to provide a friendly representation of the underlying data that will be used to create dimensions and KPIs. Essentially, it is a uniform and tabular view of physical data. Dundas Dashboard virtual tables facilitate the consolidation of data with these key features:
Wizard-Driven Virtual Table Definition makes consolidating the data easy with a step-by-step approach. The DBA simply follows the instructions and a virtual table will be created.
Support Of Multiple Data Retrieval Mechanisms will allow the DBA to leverage common methods of data retrieval, including the use of stored procedures and the manual scripting of selection queries.
Table Relational Definitions allows the DBA to define the relationship between tables using a DBA-familiar user interface. This circumvents the need to go to the database to define any table relationships.
Common Data Aggregation Functionality lets the DBA apply common grouping formulas to the virtual table without having to go back to their native database tools. Functions include:
- SUM
- AVG
- MIN
- MAX
- STDDEV
- STDDEVP
- VAR
- VARP
KPI-Friendly Data Functionality allows the DBA to hide unnecessary columns and supplement confusing column names with friendly names to facilitate the KPI definition process. For example, renaming "emp_id" to "Employee ID" gives the KPI designer a better idea of what the column represents. This added level of user-friendliness allows the KPI designer to spend less time trying to figure out the underlying data and more time on developing effective KPIs.
On a related note, it is important to understand that Dundas Dashboard was developed specifically for creating dashboards, which are, fundamentally, designed to display (and therefore not change) data. So by design, virtual tables are meant for selecting data only and not for modifying data.