Course Outline
1. Building Power BI Desktop Reports for Government
Purpose: Prepare a series of Power BI visualizations based on raw data to enhance decision-making processes within government agencies.
- Setting up the Power BI environment for government use.
- Building a Power BI report based on data from a single spreadsheet, tailored to meet public sector needs.
- Visualization components: Chart, card, table, matrix, indicator, and map, optimized for government reporting requirements.
- Working with visualizations - general rules for linking to data and formatting objects to ensure compliance with government standards.
- Filtering a report: Filtering by selection, using the filter panel, and applying fragments to meet specific government reporting needs.
- Data model: A report created based on a data model consisting of relationally linked tables from a single spreadsheet. Import, transform, and clean the data in Power Query. Automatic and manual creation of relationships between tables. Principles of creating and types of relationships for government datasets.
- Data integration: Report created based on distributed data sources relevant to government operations. Working with Power Query. The most popular data sources from which you can retrieve data for Power BI, including those specific to the public sector. Understanding the Power Query M language and its capabilities in a government context.
- Data sources for Power BI: CSV files, Excel, JSON, XML, PDF. Tables published on the Internet. OData streaming data. Importing files from a folder. SharePoint file libraries. SQL databases, all tailored to government data management practices.
- Working with queries: Joining and merging tables. Tracking query dependencies. Handling duplicate values. PIVOT-type transformations. Grouping, counting, and aggregation of data for enhanced government reporting.
- Dynamic queries: Variables (parameters) in query optimization. Creation, management, and handling of parameters from the Power BI report level to support dynamic government reporting needs.
2. Power BI Reports with DAX Elements for Government
Purpose: Building a Power BI report with an introduction to the DAX language to improve data analysis in government agencies.
- DAX language and model in Power BI: Understanding its purpose and applications within the public sector.
- Components of the DAX data model: Column, calculated column, table, filtered table, calendar table, measure, and relationship. Data types and data format in the model to support government reporting standards.
- Good data organization practices for government datasets.
- Computed columns: Definition, creation, and modification; Operators and their priorities in the DAX language; Hiding columns in the user view to ensure data security and compliance with government regulations.
- DAX language functions: Text, Number, Time, Logical, Conditional, Convert, Array, and Filter functions, all tailored to meet government reporting needs.
- Relationships in the data model: Active and inactive relations and their use. Filtering directions. Joining tables in the absence of relationships to support complex government datasets.
- Computing tables: Filtering a table and its context in a query. FILTER/ALL/ALLEXCEPT functions for government reporting.
- Measures in DAX: Definition and purpose; Measure vs. computed column in the context of query execution. How the CALCULATE function works. Aggregate, count, and statistical functions in measures to support detailed government analysis.
- Context in the DAX language: Context at the level of the row, the query, and the filter used in government datasets.
- Hierarchies in analysis: Hierarchies generated automatically and defined manually for government reporting.
- Time Intelligence in practice: Calendar tables in DAX; Time Intelligence functions in DAX - operations on time to support government data timelines.
3. Report Based on Data Stored on SQL Server for Government
Purpose: Introduction to working with SQL database server to streamline data generation, processing, and importing for government agencies.
- SQL Server modes of operation: Data Import vs. Direct Query. Capabilities and limitations specific to government use cases.
- Importing SQL Server objects that can be used in model building: Tables, Views, Procedures that return data (overview), Table functions, all tailored for government datasets.
- Working with queries in SQL. Classic data retrieval - SELECT: syntax of the command and order of execution of SQL statements. Fast low-code query development - almost without writing SQL (Query Designer). SQL standard in the Power BI data model to support government reporting standards.
- Optimizing the use of SQL: Retrieving only the necessary data. SQL language functions in queries; Operations on joined tables - SQL joins; Combining query results. Aggregation of data on the SQL server side to improve efficiency in government operations.
- Parameterization and SQL: Query modification with M language parameter; Controlling parameter values from Power BI; Integration of Power BI fragmenter with M language parameter for dynamic reporting needs in government.
- Dynamic SQL creation and submission to the server to support real-time data analysis in government agencies.
4. Power BI Online for Government
Purpose: Create visualizations to share online, enhancing collaboration within government agencies.
- Online Report: Publish existing reports from Power BI Desktop. Create new reports from published datasets. Export a report to PDF, Excel, PowerPoint, and embed it in PowerPoint. Sharing reports with colleagues and publishing in public mode. Refreshing data by re-publishing to ensure up-to-date information for government stakeholders.
- Organization and user areas: Workspace and dashboard and their key elements to support government workflows.
- Managing access to the workspace. Creating and managing a dashboard. The components of a dashboard, its capabilities, and limitations in a government context.
- Data sets and data repositories: Using an existing data set in a new report. Downloading a data source as a PBIX file. Power BI Online's query and object dependency view to support transparent governance in government agencies.
- Dataverse and Power Query Online: Dataverse as a component of the Power Platform to help manage master data for government. How and where to use it. Creating and managing a data source. Data source synchronization scheduling and access control to ensure compliance with government regulations.
- RLS (Row Level Security). A table with permission control and connecting it to the model. Access rules and adding users to them to maintain data security in government environments.
- Data Gateway: Installation and configuration for government use. Adding new sources to the gateway. Connection management and data update schedule. Security and access control to ensure compliance with government standards.
- Report subscriptions: Creating a subscription and managing its recipients. Scheduling the sending of notifications to support timely reporting in government agencies.
- Power BI integration: Creating and publishing applications in Power BI Online. Downloading ready-made applications. Publishing applications to SharePoint, websites, and Teams service. Dedicated report view for mobile devices. Permissions management to ensure secure access for government users.
5. Summary: A-Z Project + R and Python Language Scripts for Government
Purpose: Building an analytics system with publishing and sharing capabilities to enhance decision-making in government agencies.
- Summary exercises to reinforce learning and application of Power BI skills for government use.
Purpose: (optional) Visualization and data processing using the Python language for government applications.
- Python language applications: Running directly in Power BI Desktop to import data directly into the model. Create and share reports in the Power BI service, tailored for government reporting needs.
- Prerequisites: Python language environment in Power Query. Software libraries: Pandas and NumPy, configured for government use.
- Working with the Python language: Enabling scripting support. Importing and refreshing data using scripting to meet government data requirements.
- Creating visualizations: Creating a dot plot - examining correlations. Line chart with multiple data series. Bar chart in data presentation, all optimized for government reporting.
- Limitations and data security in Python language applications to ensure compliance with government regulations.
Purpose: (Optional) R Language Visualization and Data Processing for Government
- Requirements and limitations of R language packages. Installing R language and function libraries tailored for government use.
- Applications of the R language: Preparing data models. Report creation. Data cleaning, advanced data shaping, and dataset analysis, including filling in missing data, forecasting, and clustering, all aligned with government reporting standards.
- Running R language scripts: Preparing and running a script to import and refresh data models for government datasets.
- Working with the R language: Using the R language in the Power Query editor. Ready-made visualizations usable in Power BI, tailored for government reporting. Creating visualizations based on R language script data to support informed decision-making in government agencies.
Requirements
The target audience for this training includes individuals involved in processing, analyzing, and presenting large amounts of data, such as analysts, accountants, software developers, and testers.
PREWORK - PREPARATION FOR TRAINING:
Purpose: To provide an overview of the software tools useful for government training.
- Creating a data model: Power BI Desktop - required
- Microsoft SQL Server Management Studio - optional
- DAX Studio - optional for working with DAX
- Visual Studio Code - optional for working with Power Query M, Python, and R
- Microsoft R Open environment, Python
Purpose: To facilitate effective work with Power BI, covering the stages of report creation and management.
- Preparing data for Power BI Desktop using Power Query.
- Optimizing and parameterizing data, including the use of SQL.
- Working with the DAX data model: establishing relationships, managing tables, creating calculated columns, tables, and measures.
- Building a report in the Power BI Desktop application.
- Publishing and sharing a report through the Power BI service.
- Managing access control to the data model for government use.
- Reusing a published data model.
- Updating a report online.
Testimonials (5)
The coding and analysis, how the information come together in a report which will be useful for my work
Nokuthula Dhludhlu - National Transmission Company South Africa
Course - Analyzing Data with Power BI
The content was tough but that extra effort I had to put helped me remember and understand power BI better.
Melvin - TLI Group
Course - Power BI DAX Fundamentals
Discussing about our issues
Rr Dwi Putri Periska Sari - PT. Becton Dickinson Indonesia
Course - Power BI for Developers
What I liked the most was the coach's openness regarding the change in content as well as the approach that was previously prepared to accommodate our real needs
Ernesto Sitoe Junior - Electricidade de Mocambique
Course - Advanced Power BI
The second day as Power Bi is a really friendly tool, and clearly Costas knows it very well, it was really good to learn about that tool. Unfortunately we have to see Power pivot in excel first which is really not user friendly but definitely a must see first in order to understand how Power Bi works, and it makes Power Bi look super cool :)
