The architectural design is based on a service-oriented structure that enables secure and flexible interaction between a custom Power BI visual, an Azure Functions component hosted by cBI Focus, and an Azure SQL Server instance located in the customer’s tenant. The goal is to perform interactive data operations directly from within Power BI — currently limited to creating and updating records. The DELETE function is not yet implemented but is already accounted for in the architecture.
The architecture is built on three core components that communicate with each other:
- Power BI (at least Pro subscription or Microsoft 365 with the appropriate license) with cBI Focus Planner
- The user’s Azure SQL Server database
- The cBI Focus Planner backend (hosted Azure Functions)
At the core is the custom visual, cBI Focus Planner, embedded as a matrix visual in a Power BI report and serving as the user interface for capturing (planning) data. Developed using Microsoft’s official Power BI Visuals SDK, it allows users not only to visualize and consume data but also to actively interact with the database. Through UI elements such as input fields or buttons, users can trigger actions that send HTTP requests to Azure Functions, which act as the middleware. These requests contain the necessary information to perform targeted database operations.
The Azure Functions are hosted as a web service in the dedicated Azure tenant of cBI Focus. They serve as the central interface between the visual and the customer’s database. The Azure Functions receive HTTP requests from the visual, handle authentication, and execute the corresponding SQL commands. They are designed to be multi-tenant capable and can communicate with various SQL Server instances — regardless of which Azure tenant they are hosted in.
The Azure SQL Server instance resides in the customer’s Azure tenant. It contains the relevant data structures and tables accessed by Azure Functions. The connection is established via dedicated SQL logins. The database user should have only the minimum necessary permissions to perform the required operations, ensuring a high level of security.
A typical design process begins with a report creator (editor) building a planning application in Power BI using the cBI Focus Planner custom visual and then making it available to the designated end users (planners) (see here for more details to the role model). Planners are then able to enter planning data into the matrix visual or modify specific existing data. Each input is confirmed by clicking the “Save” button. This triggers an HTTP POST request to Azure Functions, including the payload. The Azure Functions process the request by connecting to the SQL Server instance in the customer’s Azure cloud tenant, executing an INSERT and/or UPDATE command, and returning the database response—success or error—to the visual. Upon successful write-back, all visuals in the report are automatically refreshed.
