What’s great about blogging is that you have a direct communication channel with customers. This is also an opportunity to answer specific questions about our products.
Last week, Claudio asked if DF can union partitions without joining the tables.
Yes, it can and I will try to explain how.
First, I created 3 connections pointing to 3 SQL Server instances containing an Order table for their own region (ASIA, EMEA, US).
I created a target table called Orders (simplified the schema (keep only 4 columns from the sources) and add a column REGION:string), then I create a mapping rule for each connection containing a single table (no joins are required).
For earch mapping rule, I entered a literal containing the region (‘ASIA’, ‘EMEA’, ‘US’).
This literal will be used by the Data Federator Intelligent Data Connection (IDC) mechanism.
By default, DF unions the 3 mapping rules and the Orders table will contains the union of the worldwide orders (ASIA, EMEA, US).
Finally, I deployed this project in the Query Server /UN catalog.
At this stage, the Data Federator Query Server behaves like a database and we can send it SQL statements.
select count(*) from /UN/targetSchema/Orders
In this case, the 3 sources (ASIA, EMEA and US) have to be accessed to compute the result and 3 connections to the sources have been created as displayed in the Query history.
Now, let’s see how the SQL statement will be used by DF to optimize the connection to the sources.
select count(*) from /UN/targetSchema/Orders where REGION = ‘ASIA’
If I add a filter on the REGION column containing our previously created literal, I will end up with the following query history where only the connection to the ASIA source has been created.
Let see what’s happen if I change the query to:
select count(*) from /UN/targetSchema/Orders where REGION IN (‘ASIA’, ‘US’)
Bingo, the intelligent data connection mechanism detects that 2 connections are required to fulfill the query.
I hope that this post will give you some ideas about how to scale your BI deployment using partitions and the Data Federator IDC mechanism.