Harness the Power of Data Through Modeling

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

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.

Query Joiner
Query Joiner

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.

Data source editing window
Data source editing window

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.

Expression Designer window
Expression Designer window

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.

Renaming a column
Renaming a column

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.

Converting column types
Converting column types

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.

Hiding a column from the data source
Hiding a column from the data source

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.

Query Filters configuration window
Query Filters configuration window

The list of conditions will differ based on the data type of the selected columns, as shown in the following image.

Filter options with different conditions
Filter options with different conditions

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.

Enabling user-based filter
Enabling user-based filter

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.

Configure user-based filter in Bold BI
Configure user-based filter in Bold BI

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.

Enabling code view
Enabling code view

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.

Dashboard parameter used in custom query
Dashboard parameter used in custom query

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.

Sharing permissions by clicking the Action menu
Sharing permissions by clicking the Action menu

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.

Leave a Reply

April 9, 2021 6 min read

Share

Access live 30-minute demo with our experts

Related Blogs

Up arrow icon
Live Chat Icon For mobile
Hugo Morris

We are here. Let’s chat!

Live Chat Icon