Power BI | Joining Keys data sets

Once you've completed your Keys implementation and have your Keys data sets, you are now ready to start creating visualizations in Power BI.

Here's a guide on how to join data sets in Power BI. 

The first thing to note is that no matter which data sets you're joining, the OneView data set always needs to be the left-most data set.

Are you using flights? If so, follow this guide. If not, skip down to the Keys without Flights section. 

Keys with Flights (v_keys_subcampaign_flight)

Open Power BI and click Transform Data. Under the Home tab, click to add New Source, select Amazon Redshift and connect using your credentials. Enter Server as: hostname:port# and select Data Connectivity Mode of Import.
Select the schema (client) you’re creating the viz for. Locate the OneView data set you're using and drag to the pane. 

 

Locate the 'v_keys_subcampaign_flight' data set and check the box click OK

In the Queries pane, click the new view table 'v_keys_subcampaign_flight'. 

Right click the start_date column header and select Duplicate Column.

Rename the new column by right clicking on start_date – Copy > Rename > start_date1

Change the data type on the start_date and end_date columns. To do this click on the calendar icon in the left corner of the column header. Change the data type to Date/Time/Timezone (this will ensure the most accurate calculation)

Next click the headers while holding Ctrl (in this order) 1. end_date  2.start_date (both columns should be highlighted). Go to the Add Column tab, select the Date button, Subtract Days.

 

You should now have a new column named “Subtraction” with the difference between the two dates. 

Next in the Add Column tab, select Custom Column. Name the column “Date”.

In the Custom column formula, copy and paste this formula:

List.Dates([start_date1],[Subtraction]+1,#duration(1,0,0,0))

Click OK. You now have a new column named “Date” 

Click the double arrows on the Date column. Select Expand to New Rows

You should now have a row for every date within the flight timeframe for each key. 

Click on the v_oneview_media dataset. In the Home tab, click Merge Queries > Merge Queries.
The original dataset (v_oneview_media) should be listed at the top of the popup screen, scroll through and click the Key header, while holding Ctrl also click Report Date. You should have two fields highlighted: Key indicated with a 1, Report Date indicated with a 2. 

In the dropdown select the ‘v_keys_subcampaign_flight’ data table. Following the same process as above, select the Key column header, while holding Ctrl also click Date. You should have two fields highlighted: Key indicated with a 1, Date indicated with a 2.

Join Kind: Left Outer (all from first, matching from second)

Click OK 

In your data table you now have a new column at the end of your table named v_keys_subcampaign_flight. Click the double arrow icon on the column header.

In the dropdown, select Expand. Check all the fields you want to include, make sure to uncheck the box “Use original column name as prefix”

Click OK. You will now have all the selected fields added to your data table. 

To remove data load duplication, in the Queries pane click on the v_keys_subcampaign_flight  table. Uncheck Enable Load. This will turn off the table so you will only get Keys data inside the v_oneview_media table.

Next on the Home tab click the Recent Sources button, in the dropdown select the database you are using.

This will open the navigation pane for you to Import the ‘v_keys_subcampaign’ view.

Click on the v_oneview_media dataset. In the Home tab, click Merge Queries > Merge Queries.
The original dataset (v_oneview_media) should be listed at the top of the popup screen, scroll through and click the Key header (make sure you select the Key column from the original v_oneview_media dataset).  

In the dropdown select the ‘v_keys_subcampaign’ data table. Following the same process as above, select the Key column header.

Join Kind: Left Outer (all from first, matching from second)

 

Click OK 

In your data table you now have a new column at the end of your table named v_keys_subcampaign. Click the double arrow icon on the column header.

In the dropdown, select Expand. Check all the fields you want to include, make sure to uncheck the box “Use original column name as prefix”

 

Click OK. You will now have all the selected fields added to your data table. 

To remove data load duplication, in the Queries pane click on the v_keys_subcampaign table. Uncheck Enable Load. This will turn off the table so you will only get Keys data inside the v_oneview_media table.

Submit changes by clicking Close & Apply. 

 

All set to begin reporting using Keys flights!

 

Keys without Flights (v_keys_source)

Example using Paid Media Dashboard

If you are connecting the 'v_keys_source' data set, use the following steps. 

Open Power BI and click Transform Data. Under the Home tab, click to add New Source, select Amazon Redshift and connect using your credentials. Enter Server as: hostname:port# and select Data Connectivity Mode of Import.
Select the schema (client) you’re creating the viz for. Locate and select the OneView data set you're using and click OK.

Click on the v_oneview_media dataset. In the Home tab, click Merge Queries > Merge Queries.
The original dataset (v_oneview_media) should be listed at the top of the popup screen, scroll through and click the Key header, while holding Ctrl also click Data Source. You should have two fields highlighted: Key indicated with a 1, Data Source indicated with a 2. 

In the dropdown select the ‘v_keys_source’ data table. Following the same process as above, select the Key column header, while holding Ctrl also click Source Name. You should have two fields highlighted: Key indicated with a 1, Data Source indicated with a 2.

Join Kind: Left Outer (all from first, matching from second)

Click OK 

In your data table you now have a new column at the end of your table named v_keys_source. Click the double arrow icon on the column header.

In the dropdown, select Expand. Check all the fields you want to include, make sure to uncheck the box “Use original column name as prefix”
 

Click OK. You will now have all the selected fields added to your data table. 

To remove data load duplication, in the Queries pane click on the v_keys_source table. Uncheck Enable Load. This will turn off the table so you will only get Keys data inside the v_oneview_media table.

Submit changes by clicking Close & Apply. 

*NOTE: Remember when adding sources in the Keys section of ChannelMix Control Center, be sure to match them exactly to the data source name in the OneView -- spelling, casing and spacing -- they must match to use as a join clause. 

 

Keys without Flights (v_keys_subcampaign)

Example using Paid Media Dashboard

If you are connecting the 'v_keys_subcampaign data set, use the following steps: 

Open Power BI and click Transform Data. Under the Home tab, click to add New Source, select Amazon Redshift and connect using your credentials. Enter Server as: hostname:port# and select Data Connectivity Mode of Import.
Select the schema (client) you’re creating the viz for. Locate and select the OneView data set you’re using and click OK.

Click on the v_oneview_media dataset. In the Home tab, click Merge Queries > Merge Queries.
The original dataset (v_oneview_media) should be listed at the top of the popup screen, scroll through and click the Key header. 

In the dropdown select the ‘v_keys_subcampaign data table. Following the same process as above, select the Key column header.

Join Kind: Left Outer (all from first, matching from second)

Click OK 

In your data table you now have a new column at the end of your table named v_keys_subcampaign. Click the double arrow icon on the column header.

In the dropdown, select Expand. Check all the fields you want to include, make sure to uncheck the box “Use original column name as prefix”
 

Click OK. You will now have all the selected fields added to your data table. 

To remove data load duplication, in the Queries pane click on the v_keys_subcampaign table. Uncheck Enable Load. This will turn off the table so you will only get Keys data inside the v_oneview_media table.

Submit changes by clicking Close & Apply.