Cloud/Network (Firewall Settings) #
CBI Focus Planner is a visual that provides a matrix visual in Power BI. The visual interacts with the tenants Azure cloud database to support company-related planning tasks. The CBI Focus planner uses Azure Functions as interface to the Azure Cloud Database. Azure Functions are a so-called compute-platform that can run code units created by us. Azure functions help to control the infrastructure within the Azure SQL Server Cloud and also perform computational tasks there. In this way, Azure functions ensure that proper changes are made to the database.
However, since the user’s SQL database and the Azure functions are located in two different Azure clouds, a connection between these two clouds must be established in order for the user to use the Azure functions of the CBI Focus Planner.
To do this, the SQL database must allow the Azure Cloud of the Azure functions to access its database. This can be set up in the firewall settings in the Azure Portal. To find this, the user must first login to the Azure Portal that they created the Azure SQL database. They then need to search for “SQL databases” in the search bar and select the SQL database they want to use for their report. Then click > “Overview” > “Set server firewall”.
Finally, at the bottom of the page, under the term “exceptions”, the user will find the field “Allow Azure services and resources to access this server”, which they must now confirm.
If users mistrust these firewall settings, it is possible to gain access to the Azure function in another way. Instead of allowing all Azure services and resources to have access, the alternative would be to select which Users have access.
To do this manually, download the Jason file with public addresses of Power Bi servers of each region. The Jason file is available for free on the Microsoft platform.
(Here the link: https://www.microsoft.com/en-us/download/details.aspx?id=56519).
The different server addresses are in one big Jason file and each region provides multiple addresses. Next, addresses of the Power BI region need to be entered in Microsoft Azure Firewall Settings as well as the addresses of the user devices and the address of the CBI Focus Planner Backend, that manages the Azure functions. The “CBI Focus Planner Backend” Azure Cloud has the IP4 address: 72.144.61.235.
SQL-Server Database #
SQL-Server databases can be set up within the Azure cloud to store, manage and access data.
An essential criterion for using CBI Focus Planner is the organizational structure of the data. The required structure is the so-called star schema, which is explained in the following.
Star schema #
The star schema is a way of structuring data models and is typically used with large amounts of data to construct more efficient SQL queries and precise analysis. This allows users to build simpler queries at higher query speeds to facilitate the creation of visualizations, reports, and dashboards. The star schema also makes the information easier to understand, allowing each user and end user to absorb the most important information in a short amount of time. It also provides the flexibility to design analyses from different perspectives by presenting facts using different dimensional tables.
Fact and dimension tables #
As the name suggests, a star schema is structured like a star. The middle part of the star, the “center” of the star, is formed by the fact table, which contains the essential data. This data is called facts because it is the foundation of the presentation, which contains the important information. The link from the fact table to each dimension table is established by a foreign key in the fact table and a primary key in the dimension table.
A dimension table, on the other hand, describes a perspective on the fact table and is therefore based on the facts from this table. They allow us to get a broader view, identify relationships, and display their importance. Dimension tables each have a primary key that links them to the fact table. This primary key must have the same values as the foreign key in the fact table to create these links.
User model #
SQL-Server provides tiered access management, which assigns users to specific roles that allow them to access only defined data. This contributes to data security and reinforces the trustworthiness of the database.
The user model describes how users can access and interact with data to run queries for reporting and analysis. In this context, user roles define who can access which data and to what extent it can be manipulated.
For this reason, CBI Focus Planner distinguishes between two types of users. These two users are understood as technical users and are also SQL Users. On the one hand, there is the DB (database) user, who creates the database on the SQL server and manages it. The DB user creates with this database a dataset and uses the CBI Focus Planner to build the Power BI report. For connecting the database with Power BI and creating a report, the report editor must log in with the DB user. Once the editor or planner is logged in with the proxy user, he can manage the rights for the writeback function. Therefore he is also able to use the writeback-function.
On the other hand, there is the proxy user that gets rights from the DB user to writeback data and save them in an appropriate way.
At the end of this chapter, it is important to mention that other requirements for uploading the SQL database are explained in chapter 3.2, where it is explained how report editors have to set up their SQL database in Power BI for using the CBI Focus Planner.
