Skip to main content

Supercharge Your BI Dashboards With Data Models

Data analysis and interpretation is the key to a data-driven business. Business Intelligence tools must provide effective methods for pulling data from data sources and presenting them in the visualizations. 

The traditional approach to query data for Wyn Dashboards allows the end-users to connect to varied data sources. However, with this approach users always rely on DBA to answer their requests for a new dataset. This makes it unsuitable for real-world business scenarios where the analysis query is not satisfied with a limited set of datasets. By the time DBA answers the data request, the analysts may have moved to the next analysis item. 

To reinforce the real-world business needs, Wyn Enterprise strengthens the dashboard with advanced Semantic Data Modeling capability. The new method empowers DBAs to expose the database objects and make them available to their business users in a controlled environment. The users can explore the available data themselves and be able to derive insights and create visualizations without the need for to and fro query requests to DBA.  

Here, we'll discuss how the Data Model feature streamlines your dashboarding experience with a Revenue Analysis dashboard.

What is a Data Model?

What exactly is a Data Model and how is it different from Data Sources and Datasets in Wyn Enterprise?

A Data Model is a semantic layer between the Wyn Server and the underlying data source. It provides users with an abstract layer of a data structure based on the Entity-Relationship model.

It allows querying the database directly at run time. No data is cached or imported to the Wyn Server, so you are always querying the latest data from the data source. The Data Model makes it possible for businesses to get up-to-the-minute data by directly accessing operational systems into a real-world business intelligence (BI) system. 

It felicitates DBA to govern the data tables, relations, views, and stored procedures from the database and make them available to the users as per the company security rules. 

You may follow the user guide for more information about the Data Models.

How is a Data Model Different from Data Sources and Datasets?

The new Data Model provides more advanced data processing capabilities than the Data Sources and Datasets as listed in the table below:

Points of Difference Data Sources and Datasets Data Model
Structure  A flat structure where a dataset represents a single data query. A hybrid structure where all the data tables are exposed to the end-users, thus allowing multiple data queries at a time.
Data caching Caches data to Wyn Server, thus needing a periodic refresh to fetch the latest data from the database. A semantic interface between the Wyn Server and the underlying database, thus always fetching the latest data from the database.
Flexibility for modifications Provides lesser flexibility to modify existing data source or dataset and get the new tables or columns added to the database. Highly flexible in allowing the addition of database objects - tables, views, columns, or even relations after a Data Model is created.
Supported data objects Tables and Stored Procedures. Tables, Views, Stored Procedures.
Preserving Database relations Not supported, relations are to be manually created at the dataset level. Primary-foreign key relations from the database are automatically generated in the data models.
Data queries vs Dashboard

One or more datasets needed must be created by DBAs for each dashboard.

Because the tables and views are fetched from the database, allows creating any dashboard with a single Data Model.  
Data from multiple tables Explicit joins are required to fetch data from related tables. Relations are fetched from the database, thus no explicit joins are required to relate two tables.

Let's see in action a Data Model based on the Contoso Retail database.

Creating a Data Model

When a DBA creates a Data Model, the interface allows selecting the tables, views from the database. Additionally, it offers to choose to import the relations defined at the database level as shown below:

Data Model in a Dashboard

When a Data Model is selected for binding visualization in a dashboard, all those metrics that the DBA allows are available from the database. When a metric from the Data model is added to a visualization, the related dimensions are highlighted (as shown below) and can be used in the dashboard (without the need to create additional dataset requests for joined queries to the DBA).

These related metrics can be used with just a simple drag-drop on the dashboard designer.

Data Model in Action

For the purpose of this post, we will use a case for creating a Revenue Analysis dashboard to analyze the cost-related metrics such as revenue and profit margin using a Data Model as shown below:

Data Model Design by DBA/IT Admins

Knowing the typical requirements for the measures and dimensions end-users are looking for, the DBA can design the data model to address those needs without the need to visualize the data. From a DBA's perspective, they know that the data needed for the typical business KPIs is stored in the data tables: DimProduct, FactSales, and FactSalesQuota of the Contoso Database.

The tables FactSales and FactSalesQuota are related to the table DimProduct; however, they are separate entities from each other, as shown below. These relations will become helpful when designing the dashboard.

The next KPI, the users, want to show is a comparative KPIs for the actual and the projected sales. The "actual sales" data is stored in the FactSales table, while the projected data is stored in the FactSalesQuota table. As aforementioned, these are separate entities. However, we can add a relation on the common column "ProductKey" in the two tables to fetch joined data. However, this does not fulfill our need as the resultant structure is not appropriate for comparative analysis.

As a DBA/IT Admin, we know that the aggregated data we want to see as a measure is available through a SQL query as below:

Select p.ProductKey, Year(f.DateKey) as Year,Month(f.DateKey) as Month, SUM(SalesQuantity) as ActualQty, SUM(SalesAmount) as ActualSalesAmount, 
SUM(SalesAmount - SalesQuantity*f.UnitCost) as ActualProfitMargin,
SUM(SalesQuantityQuota) as PlannedQty, SUM(SalesAmountQuota) as PlannedSalesAmount, SUM(GrossMarginQuota) as PlannedProfitMargin
from DimProduct p  
inner join FactSales f
on p.ProductKey = f.ProductKey
inner join FactSalesQuota s
on p.ProductKey = s.ProductKey
Group by p.ProductKey, Year(f.DateKey), Month(f.DateKey)

For the above data need, we can add an entity based on SQL expression to the Data Model as shown below:

Dashboard Design by End Users

On the user's side, I can use any or all the measures available to them in a dashboard, provided they have access to them in the data models available to them. Let's start creating a new dashboard with the DataTable visualization as it shows most of the metrics.

This table shows the profit margin gained from the sales of each product. The data presented in the visualization, namely UnitPrice, Quantity Sold, Discount, and SalesAmount are directly available from the FactSales entity in the Data Model. The ProductName column is not available from the FactSales entity but can be easily reached from the related entity DimProduct.

About the author

Paarisha Rana

As a software engineer, Paarisha enjoys GrapeCity's environment of encouragement and learning. She enjoys reading, aerobics, travelling, and exploring historical places. Paarisha graduated from Uttar Pradesh Technical University (UPTU) at Lucknow (India) with a Bachelor's of Technology in Information Technology, and you can find her on LinkedIn.

Ready to Learn More?

Request a demo with one of our embedded BI experts or get a free trial.