Adding label column for optionset in fact table

▤ Summary

In the previous post I discussed adding new columns to historical snapshots of a fact table stored in Parquet format. Alternatively, and to make the reporting much simpler, I want to add additional columns to the fact table containing the label for each choice. This work is split into these stages: • modify the notebook that generates the fact table so that future daily snapshots have the label columns populated with the correct data • create a copy of all the historical daily snapshots with the columns added if necessary, historically with a blank label (the sales team have only just started using these fields) • manually replace the existing folder of fact snapshots with the folder containing the updated snapshots This change has to do the following: • load the data for the local optionsets from the “clean” datalake • create two joins between our existing sales facts and the two optionsets, adding the label columns into the result The local optionsets dataframe has this schema: For each of the optionset columns we are augmenting, we need to join our facts to a filtered view of the optionset data: # filter out the optionset values for opportunity/ssat_category # create new column opp_categorylabel containing the label text # filter out the optionset values for opportunity/ssat_source # create new column opp_categorylabel containing the label text This is done with the same process as shown in the previous post. Many ways of doing this, the simplest of which is just done with folder renames in the storage account In the Azure portal, find the resource page for the Synapse Analytics workspace, and find the Serverless SQL endpoint: Install and open Azure Data Studio, and connect to the Serverless SQL Endpoint. This will generate a script that looks similar to this, just run it: • right click on the table that is mapped to the SQL view • if necessary, drag the new fields onto filters • save the model, and if necessary republish to any Power BI workspaces you are using

▦ Linked Companies

Microsoft

$1B+