PowerBIHybridTables

This repository is a companion repository of demonstrations that support the presentation Power BI Incremental Refresh and Hybrid Tables. In this repository you'll find:

1. SQL Scripts to recreate the AdventureWorksDW Database
2. SQL Scripts to alter the AdventureWorksDW Database for usage with the Power BI concepts of Incremental Refresh and Hybrid Tables.
3. Power BI Templates with prebuilt models and reports inllustrating these features.
4. Instructions on how to deploy this in your own environment.

Preqrequisites

This solution is built upon a combination of Power BI and Azure SQL Database therefore to leverage the code you must have access to the following:

1. A Power BI Pro Subscription (Note: if you do not currently have Power BI Pro you can sign up for a 60 day trial).
2. Access to an Azure Subscription where you can create an Azure SQL Database.
3. A machine with Power BI Desktop to open, configure, and deploy the models.

Azure SQL Database Setup

In this section we'll setup the Azure SQL Database.

Part 1 - Provision the Azure SQL Database

1. Navigate to the Azure Portal and login.

2. Select "Create a resource".

   

3. In the search box type "sql azure" and press enter.

   

4. Click "Create" for the Azure SQL Resource.

   

5. Select the Resource Type "Single database" and click "Create"

   

6. Enter and make note of the properties for:

   • Resource Group
   • Database Name
   • Server
   • Password

7. Click "Review and Create".

8. Click "Create".

9. You'll be presented with a progress screen.

   

10. Once the databse is done provisioning click "Go to Resource".

Part 2 - Configure the Server firewall

1. By default databases are not externally accessible. Click "Set server firewall" to change access settings.

   

2. Choose "Selected Networks"

3. Click "Add your client IPv4 address..."

4. Check "Allow Azure services and resource to access this server".

5. Click "Save".

Part 3 - configure the database

1. Navigate to the query editor.

   

2. Provide your login credentials and click "OK".

   

3. Copy the contents of 01 - Create AdventureWorksDW.sql from the scripts folder and click "Run".

   

4. Repeat the last step for scripts 02 - Populate dimDate.sql, 03 - Shift FactInternetSalesDates.sql, 04 - Cleanup Orders.sql paying close attention to the instructions for the dates. (Note: the script in its current form should work fine through the end of 2023, but will need attention in 2024).

Power BI Setup

Now that the databse is properly configured, you can setup the Power BI Components.

Part 1 - Setup in PowerBI.com

1. Navigate to Power BI and login.
2. Create a new workspace.

Part 2 - Open and Deploy Power BI Template

1. Open PowerBIIncrementalRefresh.pbit.

2. When prompted to provide data be sure to enter the ServerName and Database name that you used when configuring the sql server, and click "Load".

3. You must also provide RangeStart and RangeEnd values. Good values for this are 1/1/2021 and 1/1/2022 respectively.

   

4. When promped enter your databse username and password and click "Connect".

   

5. Data will now import from the source database.

   

6. Once the data has loaded successfully, click "Transform Data" to setup a custom filter for incremental refresh.

   

7. Find the FactInternetSales table's OrderDate column and add a custom filter.

   

8. Set the filter to be "is after or equal to" the parameter "RangeStart" and "is before" the parameter "RangeEnd" and click "OK".

   

9. Now that the range filter is applied, right click on "FactInternetSales" and select "Incremental Refresh".

   

10. Setup an incremental refresh policy and click "Apply".

    

11. Click deploy, and deploy the configured report to the Power BI workspace.