Publishing the data for clients

You can translate the document:

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

field	expression
totalsales	count(*)
month	getmonth(first(saledate))
year	getyear(first(saledate))
area	first(area)
in_promotion	case 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:

field expression

totalsales sum(totalsales)
Finally, click .

field	expression
totalsales	sum(totalsales)

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.