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:
If tables or views are skipped during the Data Model creation, these can be easily added later. For the sake of the sample, we will select all tables here.
The Data Model we created for the Contoso Database (after selecting the tables, views, and relations) looks like this:
Notice that all the tables, views, columns, and relations are from the database. This is because, for this example, we skipped the data security step that a DBA would typically add to Data Models. In real-world use, a DBA can control the database objects that need to be exposed to a group of users using the data governance rules that Wyn offers.
DBAs can further secure the data with row-level security without actually modifying the database.
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.
Observe that because all those metrics needed for a business are available from the database, the same Data Model can be reused for any dashboard requirement. For instance, the Retail database we used in this blog can be used for various other retail dashboards such as sales and profitability, inventory, foot traffic, and even employee performance, among many other.
However, there could be additional query needs not satisfied by the available entities (as you will see later in this article). You may contact the DBA or create it yourself (if permitted by the data security rules) for those additional query requests.
A data model significantly reduces the requests you may need to make to DBA for the new queries.
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:
On the above Revenue Analysis Dashboard, a user would want to visualize:
- Gross Revenue from the sales of a product in current FY
- Total number of items sold
- Profit the company made by selling the product
- Analysis around projected and actual revenues
- Product-wise profit margin achieved from the sales
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.
Based on the other metrics that the users would be looking for, cost of goods (COGS) and profit margin, are not available with the entities by default since the underlying tables don't store that data.
These can be calculated easily using the attributes of the FactSales entity.
For these metrics the Data Model is modified to add two new attributes based on SQL expression to the FactSales entity as shown below:
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:
At this time, the data model is ready to address the typical use case. It contains all the measures and dimensions that the business users typically use. If a new KPI needs to be added to the model, the change is as straightforward as adding an attribute, entity, or relationship to the data model.
The DBA/Data experts thus focus on providing governed data in a real-world model to business users. This is typically a one-time exercise to cover all the common data needs for end users. For the sake of this article, we have focused on just a few metrics that a user would want to see for a specific dashboard.
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.
This Real_Vs_Projected entity exposes all the remaining metrics needed for the dashboard.
When drag-dropped to the desired visualizations and formatted for display in the dashboard, the other part of the dashboard looks like below:
This is an example of a dashboard that shows the revenue data by directly querying the database rather than showing cached data.
There are many other advanced features (like row-level security that the Data Model supports). It provides a whole new experience of data analysis with Wyn Dashboards. It streamlines the process of finding data insights that are not immediately available in a flat structure provided by the IT teams and DBAs.
Paarisha Rana