Use Data Warehouse Cloud to select raffle winners #datachallengeaccepted

On November 23, 2021 we have done a social ride with Jan Frodeno on Zwift with more than 200 virtual riders joining from all over the world with a Zoom call in parallel where SAP colleagues joined to learn from Jan how he uses data.

As part of the ride – in the spirit of Black Week and Christmas around the corner – we wanted to do a raffle. Using paper was clearly no option in times of digital transformation. That is why we quickly stitched together the following data model in SAP Data Warehouse Cloud to let technology decide the winner.

I would like to share the steps and code for whoever wants to replicate that in their virtual Christmas parties or wants to learn a little bit about some of the modeling capabilities of SAP Data Warehouse Cloud.

1. Upload of Possible Winners List

I was provided with a CSV flatfile from the registration platform that I easily uploaded with the desktop file upload. I could have also put it on an S3 bucket and loaded it with the Data Flow or (ideally) would have used the OpenConnetors or the Adverity connector to load it directly from the platform.

2. Graphical View to Generate Random Number per Participant per Query

In the next step, I have created a Graphical View in the Data Builder. In this view, I have used the Calculated Column Feature to add a column that assigns a random number per registrant with the RAND() SQL function.  Each time you refresh the query, new randomization takes place. As we did not want to have registrants that win twice, we did not refresh the query during the raffle.

In this step, we could have also anonymized or pseudonymized certain information but this was not required for this use case.

3. SQL View with RANK Function in order to derive absolute value for raffle

Based on the Randon Number, I now wanted to derive an absolute value in order to let Jan guess a number to draw the winners. This can be easily done with a SQL View with SQLScript as a language.

return SELECT "First_Name",
"Last_Name",
"RandomNumber",
rank() over (order by "RandomNumber" desc) "WinnerNumber"
FROM "Registrants_Randomization";

4. Consumption View with Input Parameter To Serve Prompt in SAC

In order to retrieve the Name of the person with the rank number that Jan guesses, I create a Calculation View on top with an Input Parameter. This parameter will be promoted in the SAP Analytics Cloud Story. In order to only show the value of the input parameter, I use the Input-parameter as a filter condition (it is important to comply with the ‘:” annotation in front of an input parameter, e.g. :WINNERSELECTORJAN = WinnerNumber.

5. Story to Pick the Winners

In the last step, I now leveraged the Graphical View as a data source in SAP Analytics Cloud. I pick the input parameter as a prompt and for the sake of simplicity just show the first and last name in form of a filtered bar chart.

It took a couple of minutes and I could avoid the hat with paper snippets to draw the winners. How would you have done it? Try it out yourself with a free trial of SAP Data Warehouse Cloud: https://www.sap.com/germany/products/data-warehouse-cloud/trial.html

Anything else you want to know about this small use case? Enjoy your virtual Christmas parties!