To ensure that the described architecture operates reliably and securely, certain technical prerequisites must be met on the customer’s side. These requirements pertain to the structure of the underlying data model, the configuration of the network environment, and the SQL Server instance. In addition, specific settings within the Power BI environment are necessary to enable seamless communication between the custom visual, the Azure Functions, and the customer’s database.
The following sections describe the key requirements in detail.
Power BI Licensing #
No additional Power BI license is required to use the cBI Focus Planner within Power BI Desktop. In this scenario, an existing internet connection and the ability to download the custom visual from the Microsoft AppSource Marketplace and embed it locally into a report are sufficient. This setup is particularly suitable for development and testing purposes or for use in smaller, locally operated environments.
However, for productive use—especially when deploying the application to end users—it is recommended to use the Power BI Service. This requires at least a Power BI Pro license. Only with this license is it possible to centrally publish created reports and share them specifically with other users, such as planners. This becomes especially important when multiple users are working with the visual simultaneously or when centralized management and control of content is desired.
Regarding data connectivity, both Import mode and DirectQuery are supported in Power BI Desktop and are compatible with cBI Focus Planner. However, in the Power BI Service with a Power BI Pro license, DirectQuery is mandatory if automatic visual updates after data write-back are required. If Import mode is used instead, data changes can still be saved, but the visuals in the report will not update automatically—significantly limiting the user experience.
If you still wish to use Import mode in the Power BI Service with automatic updates, a Power BI Premium license is required. This license enables access to an XMLA endpoint, which allows Import models to be refreshed via a technical interface.
See the following overview for details on Microsoft’s available plans and licensing models: Power BI: Pricing | Microsoft Power Platform
SQL Server Configuration #
The current version of cBI Focus Planner exclusively supports Azure SQL Server instances. Other database technologies or on-premises SQL Servers are not supported at this time.
To ensure successful operation, both the network connection and the underlying data model must meet specific requirements, which are described in the following sections.
Network Requirements and Firewall Configuration #
To ensure reliable communication between the Azure Functions, Power BI, and the Azure SQL Server instance in the customer’s tenant, appropriate firewall rules must be configured. Specifically, the IP addresses or IP ranges of the Azure Functions and the Power BI service must be allowed access to the database.
There are two main options for configuring IP access:
- Enable the option “Allow Azure services and resources to access this server”
This setting allows all Azure services and resources to access the SQL Server—at least at the network level. It is important to note, however, that valid SQL database user authentication is still required despite this access. This option provides a quick and easy way to establish connectivity but should be used with caution. - Explicit whitelisting of individual IP addresses
A more secure and targeted approach is to allow only the specific IP addresses that are actually needed. These include:- The public IP address of the device running Power BI Desktop
- The IP ranges of the Power BI service (these are regularly published by Microsoft)
- The IP address of the cBI Focus Planner Azure Functions
These permissions can be configured directly in the Azure Portal or via a Bash or PowerShell script.
Enabling the Option “Allow Azure services and resources to access this server” #
- Sign in to your Microsoft Azure account and select the desired SQL database.
- Click on „Overview“.
- Choose the Option „Set server firewall“.
- On the new page, scroll down to the bottom.
- Under the “Exceptions” section, enable the checkbox “Allow Azure services and resources to access this server“.
- Click „Save“.
Explicit Whitelisting of individual IP addresses #
- Visit the following website: https://www.microsoft.com/en-us/download/details.aspx?id=56519.
- Download the JSON file containing the current list of Microsoft service IP ranges.
- Search for the region assigned to your Power BI service (i.e. PowerBI.WestEurope).
- Sign in to your Microsoft Azure account and select the desired SQL database.
- Click on „Overview“.
- Select the option „Set server firewall“.
- Add the CBI Focus Planner backend IPv4 address: 72.144.61.235
- Add the relevant Power BI service IPv4 addresses to your firewall settings.
- Add the IPv4 addresses of the users who need access to the database.
Note: The IPv4 addresses of the Power BI service change periodically.
Data Modeling: Star Schema as a Requirement #
To integrate with cBI Focus Planner, a dimensional data model in the form of a star schema is required. The star schema is a widely used database structure, particularly in data warehousing and business intelligence. It supports multidimensional data modeling and is designed to optimize query performance and simplify data analysis. The key characteristics of the star schema are as follows:
1. Fact and Dimension Tables
Fact Table: This central table contains the quantitative data (transactional data) to be analyzed, such as sales figures, revenue, or other key performance indicators. Each row in the fact table represents an event or transaction.
Dimension Tables: These tables contain descriptive attributes (structural data) used to analyze the facts. Typical dimensions include time, product, customer, and location. Each dimension table has a unique key column that links to the fact table.
2. Simple Structure
The star schema features a clear and intuitive structure, with the fact table at the center and the dimension tables arranged around it like spokes. This layout simplifies data navigation and understanding.
3. Clear Relationships
Each dimension table is directly linked to the fact table, meaning there are no complex joins between dimensions. This direct relationship improves query performance and simplifies SQL queries.
4. Denormalization
Dimension tables are often denormalized, meaning they may contain redundant data to enhance query performance. While this increases the size of the dimension tables, it typically results in faster query execution.
5. Simple Queries
Thanks to its clear structure and direct relationships, queries in a star schema are generally easier to write and understand.
Structure of the Star Schema #
- Fact Table:
The fact table is at the center of the model. It contains the numerical key figures to be analyzed as well as the primary key, which is a composite key referencing the various dimension tables. A surrogate key is used to uniquely identify each data row in the fact table. - Dimension Tables:
These tables contain descriptive attributes used to categorize and filter the facts, such as “Time,” “Products,” or “Customers.”
Purpose in the Context of cBI Focus Planner #
cBI Focus Planner requires a structured data model to perform targeted write-back operations. Only when the origin of a value can be clearly identified is precise data updating possible. The star schema ensures that:
- Every value displayed in the visual is uniquely identifiable
- No ambiguities occur during the write-back process
- Performance and usability are maintained even with large data volumes
The use of star schema relationships is therefore a mandatory requirement for the correct and reliable operation of cBI Focus Planner, as it enables the visual’s write-back logic.
User Configuration #
To ensure secure and controlled data exchange between Power BI, the cBI Focus Planner backend, and the SQL database, two separate user roles are defined: the READ user and the WRITE user.
READ User
The READ user is responsible for establishing the connection between Power BI and the Azure SQL Server. This user is granted read-only access to the database.
It is important to note that a dedicated database user is not strictly required for read access. Instead, Power BI supports native authentication methods, allowing for a flexible and user-friendly connection to data sources.
Available authentication methods are:
- Windows Authentication
- Microsoft Account
- SQL Database User
It must be ensured that at least one READ user exists who can authenticate with the Azure SQL Server using one of the methods listed above.
WRITE User
The WRITE user is primarily responsible for writing data back to the database, but is also used for queries required for validating the modelling of the cBI Focus Planner.
Due to technical limitations in Power BI, it is not possible to forward the logged-in Microsoft or domain user credentials for write operations. Therefore, additional configuration is required within the cBI Focus Planner settings (see here). To enable this, at least one SQL database user must be created in Azure SQL Server and granted the necessary permissions.
It is recommended to restrict this user’s access rights to only those data areas that are relevant for planning or control purposes. This ensures that no unauthorized write access to sensitive data can occur.
The minimum required permissions for the database user are:
- SELECT rights for the fact table and all dimension tables
- INSERT and UPDATE rights for the fact table
cBI Focus Planner Licenses #
For initial testing, a publicly accessible sample report is available via Microsoft AppSource. This report includes a fully preconfigured demo application with the cBI Focus Planner embedded, and it can be used without a purchased license. The write-back functionality is also active within this sample report, allowing you to realistically test how the visual works.
Please note that the write-back functionality only works with the demo database included in the sample report. Connecting to your own SQL Server instance is not possible in this mode. A valid license is required to connect to your own databases.
For more information about the licensing model and pricing, please refer to the product page on Microsoft AppSource: cBI Focus Planner
