Business intelligence is a complex yet increasingly important subject in the business landscape. Users need to analyze data, then transform it from raw data points into meaningful visualizations. This process can be challenging, especially as businesses generate datasets ranging in size from a few rows (such as transactional sales data) to millions of rows in predictive data models to forecast future decisions.
As the size of the required data grows, the need to filter it becomes more relevant across stakeholders so that only the data pertaining to a specific business interest is used to make data-driven decisions.
Earlier versions of Wyn Enterprise offer data filtering within the Datasets (or ad hoc query) by creating simple rules on data fields and expressions to extract the specific information from the original data.
With Wyn Enterprise, 4.0, Dataset filters have progressed to support more complex query filters. You can now group by the same simple rules, as well as create hierarchies for filtering conditions. You can also choose the operator “AND/OR” for grouping these rules.
In this post, we explore the new interface for adding filters to a dataset.
Suppose the CEO of a company wants to analyze the product sales for the current fiscal year from different regions through a dashboard and/or report. The schema of the data in the database is as shown below:
The CEO also wants to share this sales analysis with the company's Regional and Store Managers, but he/she wants to restrict the data so that these managers receive only the data relevant to their respective region and/or stores. According to this business requirement, the data should be made accessible to different users across the company's hierarchy as depicted below:
To be able to fetch data using the Wyn Datasets for the above scenario, we will need an ad hoc query that looks something like the below SQL query:
Select * From Sales where YearofSales = Current Year AND ( ( Region = @Region AND RegionalManager = @UserName ) OR ( StoreID = @StoreID AND StoreManager = @UserName ) )
Let's walk-through the steps to visually design this filtered query using Advanced Filters in Wyn's Dataset Designer.
Before we proceed with designing the filter query, let's take a look at the UI and the main components of the Advanced Dataset Filter Editor.The UI of the Advanced filter functionality is shown below with descriptions for each major function:
There is no limit on how many rules or groups can be used in the Advanced Filters, so users can meet any complex business requirement.
To design the query for the above use-case using the Advanced Filter editor we will need:
To follow the steps for adding the above filter conditions, you will need a Wyn Dataset. You can either create a new Dataset using the attached excel "Sales.xlsx" as its Datasource provider or simply upload the attached datasource/dataset files - "Sales.dsc" and "YearlySales.dst" to the Wyn Portal.
Once you have created a dataset for the Sales data, we can now add the rules and groups.
The filter condition for "Year" is a simple rule outside of any group. Simply click "Add Rule" and select the "YearOfSales" column to filter the data on a specified value. For this value, you can either use a constant/static value, or a parameter. For this use-case, we want it to be dynamic, so we will use a parameter with a default value of the current year, 2020.
Once done, the filter rule appears as follows:
The next rules in the Filter condition will be grouped to give resulting data when either of the conditions are met. The resulting data will then be combined with the data from the "Year" filter. As a result, we need to add three Filter groups: one as a top-level group, and two as sub-groups. .
To add the top-level group first, click "Add group". The resulting structure should be displayed as shown below:
Groups in the Advanced Filter mode must always have a rule or sub-group. Thus, when adding a group, the group will automatically be given an empty filter.
In this use-case, we do not need this default rule, so we can delete it by clicking the "Delete" icon on the right side of the empty filter, however only after adding the sub groups as described below:
Once the top-level group is added, the next step is to add the sub-groups. Click "Add group" again, ensuring that the sub-group added is in the correct level of the hierarchy. Do this step twice, since we need two sub-groups for our use-case. Each sub-group should be added with one rule by default, but we will need two rules in each sub-group. Click "Add rule" in each sub-group and delete the additional rule in the top-level group to achieve the desired filter hierarchy, as shown below:
The final step in building the advanced filter query is to bind the data columns in the rules against which data is to be filtered, and then update the AND/OR operators separating the rules and groups. Once complete, the final filter query with the appropriate rules should appear as shown:
Since our use-case should filter the data based on whether a Regional Manager or Store Manager is logged in, and which user that is, the parameters added here should be based on User Context and Custom Properties to fetch and filter the data dynamically.
Once the filtering conditions are saved to the dataset, the query retrieves the data specific to those conditions. This filtered query composing the dataset can now be used in Dashboards and/or Reports.
When the above-filtered query conditions are executed for the dataset, data corresponding to the currently logged-in user is fetched, as shown below:
The above image shows the data returned after filtering, when the data is accessed by a Regional Manager (e.g. "John Conner"). This Regional Manager sees the sales for all stores operating in his specific region. The above image shows the data returned after filtering, when the data is accessed by a Store Manager "James Johnson". This Store Manager sees the sales for the specific store he is operating.
The advanced filters in Wyn Datasets are designed to be able to structure any complex filter query for your business requirement with its intuitive and easy-to-use editor. However, if you need any assistance to design the filter query for your business requirement, reach out to us at Wyn.Experts@grapecity.com.
Wyn is a web-based BI and data analytics platform that provides greater insight into your data.
Wyn offers built-in tools for report and dashboard creation, data governance, security integration, embedded BI, automated document distribution, and a business-user-friendly interface for self-service business intelligence.