Change the sample database for Fabric Accelerator to SQL database in Fabric

Upon request, I show how you can change the sample database for Fabric Accelerator to SQL database in Fabric in this post. Instead of the suggested Wide World Importers Azure SQL Database. Right up until the stage that you run the sample Data Pipeline with this database. As shown below.

Doing this means that you can cleanly manage the sample database within Microsoft Fabric and save the trouble of deploying an Azure SQL Database.

For those yet to discover Fabric Accelerator, it is the metadata driven framework accelerator solution for Microsoft Fabric developed by Benny Austin.

To follow along with the below make sure you fork or clone the fabric-accelerator GitHub repository first. I recommend installing Visual Studio Code. Ideally with GitHub Copilot in Visual Studio Code configured to help quickly resolve any potential issues.

Creating the new sample database for Fabric Accelerator

First you need to deploy a SQL database in Fabric item in a new workspace. Then load sample data into it. Easiest option is to load AdventureWorks sample data like in the below example.

Afterwards, perform the same steps as in step three of the Fabric Accelerator deployment guide. With the below changes:

• Grant the service principal permissions to the SQL database in Fabric instead.
• Get the server and database names for your SQL database in Fabric.
  One way to do this is by going into the database editor, selecting “Open in” and then one of the applications. Afterwards, go to “Manage connections and gateways” in Microsoft Fabric and add a new SQL Server connection with those details. Make a note of the created Connection ID.

Change the sample database for Fabric Accelerator to SQL database in Fabric

First navigate to workspace->pipeline-elt-framework->wwi-elt-framework.DataPipeline. From there, make the below changes in the sections for the “Get WWI PK cols” and “Get WWI PK cols” activities.

• Change the database from WideWorldImporters to your full database name.
• Change the Connection Id to the one you created earlier.

Below is an example of what to change.

        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "select \n\t'DemoDB' as [SourceSystemName]\n\t, t.name as [StreamName]\n\t,'DemoDB' as [SourceSystemDescription]\n\t,'SQL DB in Fabric' as [Backend]\n\t,(s.name + '.' + t.name) as [EntityName]\n\t, c.name as [DeltaName]\n\t, (CASE WHEN c.name is not null then cast('2013-01-01' as datetime)\n\t\tELSE cast('1900-01-01' as datetime)\n\t END) as [LastDeltaDate]\n    , (CASE WHEN c.name is not null then 129600 --90days\n\t\tELSE NULL\n\t END)  as [MaxIntervalMinutes]\n\t,'Files' as [DestinationRawFileSystem]\n    ,'raw_bronze/DemoDB/'+ s.name +'/'+  t.name +'/YYYY-MM' as [DestinationRawFolder]\n    ,s.name +'_'+ t.name + '_'+ 'YYYY-MM-DD_HHMISS.parquet' as [DestinationRawFile]\n    ,3 as [MaxRetries]\n    ,cast(1 as bit) as [ActiveFlag]\n    ,cast(1 as bit) as [L1TransformationReqdFlag]\n    ,cast(1 as bit) as [L2TransformationReqdFlag]\n    ,cast(0 as bit) as [DelayL1TransformationFlag]\n\t,cast(0 as bit) as [DelayL2TransformationFlag]\nfrom sys.tables as t\ninner join sys.schemas as s\n\ton s.schema_id = t.schema_id\n\tand s.name in ('SalesLt')\nleft join sys.columns as c\n\ton c.object_id = t.object_id\n\tand c.name='LastEditedWhen'",
            "queryTimeout": "02:00:00",
            "partitionOption": "None",
            "datasetSettings": {
              "annotations": [],
              "type": "AzureSqlTable",
              "schema": [],
              "typeProperties": {
                "database": "DemoDB-00000000-0000-0000-0000-00000000000"
              },
              "externalReferences": {
                "connection": "00000000-0000-0000-0000-00000000000"
              }
            }

I deliberately left the select statement in the above example to highlight the next step. Which is that you should change all WWI and Wide World Importer references in this file as well. In order for your pipeline to reflect the fact it works with another database. This includes the activity names.

Plus, change any references to Azure SQL being the backend to SQL database in fabric. Like in the below extract.

"sqlReaderQuery": "select \n\t'DemoDB' as [SourceSystemName]\n\t, t.name as [StreamName]\n\t,'DemoDB' as [SourceSystemDescription]\n\t,'SQL DB in Fabric' as [Backend]\n\t,etc'"

Note: I encountered an alignment issue when I changed all the WWI references in Visual Studio Code. However, I quickly resolved the issue thanks to GitHub Copilot in Visual Studio Code.

After performing these changes rename the pipeline folder itself to something that aligns with your database. I opted for DemoDB-elt-framework. Afterwards, go into the “.platform” file and change the displayName value to match.

Changes in the GitHub Actions workflow

When finished with the pipeline I recommend going to the “deploy-fabric-accelerator.yml”. From there, make the following changes.

First, add another variable to represent the old WideWorldImporters database.

 OLD_WIDE_WORLD_IMPORTERS_DATABASE: WideWorldImporters

Afterwards, go to around line 243 and add the below code snippets just be thorough:

# CL Added to change old demo database name
echo "Files to be updated from $OLD_WIDE_WORLD_IMPORTERS_DATABASE to $DEMO_DATABASE_NAME"
grep --null -rl "$OLD_WIDE_WORLD_IMPORTERS_DATABASE" "${GITHUB_WORKSPACE}/workspace" | tr '\0' '\n'
grep --null -rl $OLD_WIDE_WORLD_IMPORTERS_DATABASE ${GITHUB_WORKSPACE}/workspace | xargs -0 sed -i "s/$OLD_WIDE_WORLD_IMPORTERS_DATABASE/$DEMO_DATABASE_NAME/g"

# # CL Added to change WWI references to DemoDB
# echo "Files to be updated from WWI to DemoDB"
# grep --null -rl "WWI" "${GITHUB_WORKSPACE}/workspace" | tr '\0' '\n'
# grep --null -rl "WWI" "${GITHUB_WORKSPACE}/workspace" | xargs -0 sed -i "s/WWI/$DEMO_DATABASE_NAME/g"

# CL Added to change WideWorldImporters references to DemoDB
echo "Files to be updated from WideWorldImporters to DemoDB"
grep --null -rIl "WideWorldImporters" "${GITHUB_WORKSPACE}/workspace" | tr '\0' '\n'
grep --null -rIl "WideWorldImporters" "${GITHUB_WORKSPACE}/workspace" | xargs -0 sed -i "s/WideWorldImporters/$DEMO_DATABASE_NAME/g" || true

You can keep the WIDE_WORLD_IMPORTERS_CONNECTION_ID variable and just put in your new Connection Id there. However, to be thorough you can change it and all the references to it in the GitHub Actions workflow instead.

Once done, you can pick up where you left off in step four of the deployment guide. Deploying with the new deployment method that i covered in my previous post. Remembering not to change the FABRIC_SQL_DB_NAME to the name of your sample database. Because that is a different database.

After your deployment, you can go into the new workspace and find the relevant pipeline. Which in my case is DemoDB-elt-framework. From there, select Run. If all is well it should complete like the below example.

You can then continue to explore the accelerator in the controldb-fabric-accelerator database.

Final words

I decided to change the sample database for Fabric Accelerator to SQL database in Fabric to avoid deploying another Azure resource and keep as much in Microsoft Fabric as possible.

I hope this helps others to do the same. Because I know how hard it can be to get resources deployed in certain environments.

You can also look to apply this in similar solutions which reference Azure SQL Databases as well. By experimenting with changing the file contents and references.

Feel free to like and comment on this post wherever you found it. Because I had no idea it would be in demand until others indicated interest online.