Publishing the data for clients

Creating final reports

With the base business entities in place we can begin to answer business questions with the data.

Combining sales and marketing data

The first step is to combine the two main business entities that we have created so far, marketing and sales data, so we can analyze the correlation between the sales information and specific marketing promotions.

This is again a join, so follow these steps:

  • Right-click over the "04-reports" folder and select New > Join

  • Drag the views "sales_full" and "marketing_full" onto the workspace.

  • Connect the "sales_full.promotion" column to the "marketing_full.promotionid_0" column, using drag & drop.

  • Now a very important part; as the promotion field in the sales table will be non-null for sales under a promotion but null for organic sales, we need to change the join type so it does not filter out the organic sales. Assuming that you dragged the "sales_full" table first, we will do this by using a left outer join.

    Just move to the Join conditions tab and then click on "Inner join", then "Change join type", then "Left outer". This will ensure we receive all of the sales rows.

  • Last, in the output tab rename the view to "full_sales_report" and remove the duplicate fields so we are left with the following schema:

Clicking will create the view. This view contains the full sales and marketing data; when we execute it Denodo retrieves the information from all the sources in real time and combines it to provide you the full view of the data.

Creating the final business reports

Once the full sales and marketing data is combined and we have a comprehensive, fully denormalized view of all our data, we can create the final report that we will use for answering the questions asked by our business requirements. What we want to get is a view of the total monthly sales under each promotion, split by country, for both organic sales and sales under marketing promotions.

We do this by using an aggregation:

  • Right-click on the "04-reports" folder and select New > Selection.

  • Drag the "full_sales_report" view onto the workspace.

  • Now, move to the Group by tab and select "Use group by". With the group by enabled, we will add the following fields as group by fields:

    • full_sales_report.country (selected from the list on the left side).

    • A new aggregation expression with the following specification:

      case when (description is null) then false else true end
    • Another two aggregation expressions with the expressions:

      getyear(saledate) getmonth(saledate)

    With these group by fields we are aggregating all the monthly sales per country, divided in two groups: one for the promotion sales and one for the organic sales.

  • Let's move to the Output tab. Here, rename the view to "monthly_sales_country" and add the following aggregation expressions

    fieldexpression
    totalsalescount(*)
    monthgetmonth(first(saledate))
    yeargetyear(first(saledate))
    areafirst(area)
    in_promotioncase WHEN (description is null) THEN false ELSE true END




  • And finally click to create the view.

If we execute it we will get these results:

You can see that for each country and each month, we have two rows: one for value true and other for value false for the "in_promotion" column, and then the total number of sales for each one of them.

Now we will create a summarized version of this report that aggregates the information per geographical area (North America and Europe). We could easily do this in any of the usual BI tools, but doing it in the virtualization layer means that it will be available to all client applications, so we don't have to replicate this logic across different tools.

  • Start by creating another selection view in the folder "04-reports".

  • Drag the "monthly_sales_country" onto the workspace.

  • Move to the Group by tab, check "Use group by" and then select as the group by fields all of them except country and totalsales:

  • In the output tab, rename the view to "monthly_sales_area" and add one aggregation field:

    fieldexpression
    totalsalessum(totalsales)

  • Finally, click .

Executing this view shows a similar result to the country sales, but now we have a lot less rows as we are aggregating all the countries per area together.

Publishing the data for clients

In this example we are going to access our report from Tableau. This BI tool uses ODBC to access Denodo and send standard SQL queries. By default Denodo offers all the views (base or derived) through JDBC and ODBC automatically; we only need to do manual publishing steps in the case of creating web services.

So in this case, this step is trivial: we do not need to do anything regarding report publication, we only need to consume it from Tableau.