Harness the Power of Data Through Modeling
Data modeling is the process of creating relationships among multiple tables, which helps you distinguish the conceptual representation of data objects, the association among different data objects, and the rules. Data modeling helps you organize and visually represent the whole of your data or just parts of it to show connections among data points and structures and get insights from them to achieve your business goals.
Bold BI helps data analysts connect various data sources and allows data modeling, like joining, filtering, and adding more functions. Then users can visualize the data in a graphical manner to see the trends in it, which lets them make better decisions about their business. In this blog post, you will see the different data modeling operations supported in Bold BI and their uses in harnessing the power of your data. You’ll be able to configure and share them with other users in the Bold BI application.
Supported data modeling operations in Bold BI
- Establish relationships among different tables
- Create calculated fields with an expression editor
- Transform raw data into a structured subset of data by formatting the columns and applying a filter
- Enable row-level security through user filters or query parameters
- Share data model with other users
Data modeling operations performed in the Bold BI application will not affect the original database connected to it.
What is a data source?
In Bold BI, a data model is referred to as a data source, which acts as a bridge between the underlying data and the dashboard widgets. It can be composed of one or more tables from the same or different data sources.
Establish relationships between different tables
When you need to use more than one table in your data source design, you have to create relationships between those tables.
Joining two tables from the same data source
Let’s consider the example data of the Northwind Traders company, and say a sales analyst wants to track the popularity of different products sold. The data analyst can make relationships between the Products and Suppliers data and create a unified data model with which the sales team can prepare a dashboard.
With the Query Joiner, you can join these tables using the Inner join with the tables’ respective keys, as shown in the following image.
Joining tables from multiple data sources
Joining data from multiple data sources is a common requirement for various online applications. One example is using data from Google Analytics and Salesforce, which is discussed more in the blog post, Track Your Website Conversions and Sources in One Place. If a marketing analyst needs to track customer conversions and their traffic sources, they need to integrate both Salesforce and Google Analytics data in their dashboard.
You can create a new data source by joining the previously created two data sources. Bold BI allows you to join tables in two or more different data sources provided they were extract-mode connections.
To combine multiple data sources, use the Shared Tables panel at the bottom of the data source designer. This pane displays the data sources you have permission to access. By default, the data sources you created previously for this or any other dashboard will be listed here. You can drag them to the canvas, as shown in the image, to join them.
Create calculated fields
Every dashboard built on top of the same data may have distinct KPIs. Because of this, it is not possible to have all derivatives associated with the underlying data itself. With calculated fields at the data model level, you can make simple arithmetic operations, complex mathematical and logical operations, type conversions, and more. Also, you can create a calculated field with measures or dimension fields from the underlying data source and built-in functions. Then, easily configure that field to certain widgets.
Let’s consider Xero finance data as an example. Accounting and executive leaders need to ascertain how much money is owed to the company versus how much the company owes. You can calculate accounts receivable and payable metrics with the calculated expression shown in the following image.
Transforming raw data into structured and formatted data
You can optimize your data by renaming columns, converting column types, hiding unwanted columns, and filtering the records at the data source level.
Renaming columns
You can rename a column with a user-friendly name to show an understandable and meaningful column.
Let’s consider a Google Ads data example. Columns are fetched with a short name while fetching data through the API. You can rename this existing column, ga: CTR, to Click-Through Rate, as shown in the following image.
Converting the column type
You can change the existing column type to Number, String, or Date.
In the Google Analytics data, the Measure column values are fetched as string type when connecting data through the API. You can change the column type from string to number and use this column to visualize the data.
Hide a column from the data source
A column may not be required in a specific data model. In that case, removing the column in the underlying data itself is not a good idea. You can hide the column at the data model level instead.
In example Pipedrive deals data, all columns are retrieved while connecting through the API. The pic_hash (data_creator_user_id) column is not needed for the showcase, so you can hide the column from the data source. You can click on the eye icon to hide the column, as shown in the following image.
Filter the records
Data-source-level filters help you restrict the visibility of records based on defined criteria. You just have to add a filter condition.
In example Northwind Traders sales analysis data, you can restrict a product whose order count is more than 0. To apply this, set the UnitsOnOrder column in the Query Filters window as shown in the following image.
The list of conditions will differ based on the data type of the selected columns, as shown in the following image.
Check out our data filters documentation for more details on configuring filters for your data model.
Restrict data access based on user
You can restrict access to data based on users by configuring user filters.
User-based filtering is the process of imposing row-level security on the underlying data, thereby giving user-based data access. This blog provides a brief discussion on user-based filtering.
As an example, let’s consider FreshBooks finance data. A company’s management decides to provide access to the dashboard for each country’s managers. The data analyst can restrict what data each manager sees based on the user at the data-source level, as shown in the following image.
Enabling row-level security with query parameters
Dashboard parameters help you replace any constant values you have used in the dashboard. They help you avoid adding values manually and save you time when handling large data sets. They can be configured manually by adding a constant value or importing a list of dynamic values from the data source.
Now let’s see how to use a dashboard parameter as a query parameter in a custom query-based data source. To use a dashboard parameter in a custom query, you need to access the table schema of the connected database server through the SQL query.
To run your custom SQL query, switch to the code view by enabling the slider in the tools pane as shown in the following image.
Now, I’m trying to retrieve the orders based on an Employee ID whose value is 4. You can create a dashboard parameter and add it to the query editor by clicking the @ symbol and selecting the desired parameter.
Finally, to save your data source, click Save at the top-right corner of the data source designer window. Now, the data model is ready.
Sharing the data model with other users
You can share this data model with your team by enabling access permissions in the application, letting them build dashboards from the model. To share the data source with other users, click Actions in the data sources menu and select Sharing Permissions, as shown in the following image.
To learn more about sharing and removing access to a data source, refer to this help documentation.
Conclusion
We hope you now have a better understanding of creating a data model and sharing it with others. You can get a free 30-minute demo of Bold BI with our experts to discuss creating dashboards and any other features you would like to learn more about. If you have any questions on dashboard parameters, please feel free to post them in the following comment section. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.
Originally published at https://www.boldbi.com on April 9, 2021.
