Closure Report
Project Summary
This project will oversee the adoption of the centrally supported Power BI Gateway demonstrated through CAHSS data sources and dashboards. In particular, it will establish links and data feeds to provide an interface to the dashboards;
- School and Programme Quality System (SPQS)
- Inventory System
The current test Power BI Gateway service is running on a tactical server which is inappropriate for wider Power BI services.
The Power BI Gateway is required to securely handle data connectivity and data transfer between on-premise data and cloud services. The gateway is an integral component of Power BI services that supports;
- Scheduled data refresh for on-premis datasets imported to the Power BI Service
- Use of DirectQuery to query data from the Power BI Service
- Use of Analysis Services, Live Connections, to query data from the Power BI Service
When implemented, the gateway will be fully supported in the production environment. Test
A description of Power BI Gateway.
Scope
The scope of the project includes; the build of development and production servers together with the implementation and configuration of Power BI Gateway Services. The scope also includes the creation of production services which will be covered under formal SLA and OLA. It does not include the installation of Power BI licenses.
Analysis of Resource Usage:
Staff Usage Estimate: 46 days
Staff Usage Actual: 33 days
Staff Usage Variance: 32%
Outcome
|
Number |
Description |
Priority
|
Outcome |
|
O1 |
To create compute environments to support Power BI Gateway service. |
|
|
|
D1.1 |
Build the compute environment necessary for the implementation and support of the Power BI Gateway Service. |
Mandatory |
Achieved |
|
O2 |
To install Power BI gateway across compute environments |
|
|
|
D2.1 |
Install Power BI gateway on the newly commissioned servers residing on the corporate network. |
Mandatory |
Achieved |
|
O3 |
To configure Power BI Gateway Services supporting CAHSS dashboards ; SPQS and Inventory System. |
|
|
|
D3.1 |
Configure Power BI Service data sources for the gateway and dashboards for;
|
Mandatory |
Achieved |
|
O4 |
To configure data sources and refresh cycles for CAHSS dashboards in O3 |
|
|
|
D4.1 |
Set-up dataset to utilise data refresh schedules. |
Mandatory |
Achieved |
|
O5 |
Implement required access controls |
|
|
|
D5.1 |
Implement Identities, credentials and permissions to be used by the data gateway. Individual users to be identified as part of the project. |
Mandatory |
Achieved |
|
O6 |
Provide Power BI gateway as a fully supported production service measured by SLA. |
|
|
|
D6.1 |
Create SLA and OLA for the production service. |
Mandatory |
Achieved |
Benefits
The benefits of the approach are to provide a Power BI production service that will be owned by IS and will have the appropriate service and operating level agreements (SLA and OLA) in place implemented in production strength environment.
Creates the basis for a service usable across the University.
Success Criteria
SPQS and Inventory databases are connected to the Power BI service via the Power BI Gateway and are available for the College BI team to create Power BI dashboards for end-users.
SPQS and Inventory dashboards are connected via the Power BI Gateway.
Gateway services are enhanced with formal support in place.
Service issues are resolved by IS as measured against SLA and OLA.
The gateway service is re-usable among other BAPI applications across the university.
Explanation for variance
The project required much less effort to complete the tasks across all deliverables. Implementing the gateway was much less complex that had been anticipated or planned. SLA and OLA's are inherited from Office 365 Services for which Power BI Gateway is a small component of this wider service.
Key Learning Points
Integrating the Gateway into Office 365 required additional testing to achieve an acceptable level of user access and security. To be included in dashboards and reports, user permission to access data sources have to be administered by the help desk. This supports the appropriate level of segregation of duties.
The use of flat files, Excel workbooks, is not the intended to use the gateway and dashboards requiring this data type should use shared workspace areas for storage of these file types.
Using the gateway to refresh reports and dashboards requires significant configuration for the user. Appropriate gateway, database access and permissions together with client changes are required to build and connect to new dashboards and reports.
Additionally, knowledge of database tables and advanced SQL skills are required to produce meaningful reports and analysis.
Use of Power BI and the gateway against raw, not normalised sources is therefore restricted to BI/MI professional practitioners. Use against data warehouse Presentation layers is less complex as the data is normalised in the access layers, but does require database skills. Mass consumption of the gateway is not recommended at this time.
Without roles based access controls (RBAC) user groups require to be set-up that support data source access levels at multiple levels e.g. Read, Restricted and Confidential. This applies equally to data warehouse access.
A lessons learned workshop was held and echoed the points noted above and also made the following recommendations;
It is recommended that , to streamline the request process and remove client and user complexity, a virtualised server environment be provisioned.
It is also recommended that further development of the service be incorporated into the project Business Intelligence and Enhancement Service DTI032.
Outstanding Issues
None.