Blend view combines data from two or more tables of the same source. Data is at the different level of details and sometimes a data set might have more or less granularity than the other table.
Blend View allows you to aggregate data from multiple tables into a single view depending on the join condition and the columns selected.
You can perform join on Oracle, MSSQL, MYSQL and SAP sources. Follow the steps below to create a blendview.
Step 1: From Profile > profiling > Create View > select Blend View and Source
Step 2: select 2 or more tables. To create a join condition between them, hover & click on one table and drag to the other.
The Join Configuration pop up will provide you the option to select inner, outer, left or right joins and the columns of both tables where join condition is to be applied.
Once you save the join configurations, the translated join condition will appear on the bottom. You can validate the query to see if its correct. (this is optional)
The UI interaction provides better user experience but an advanced user can simply provide the Join Query as well. As soon as you start typing query in the "Translated Join Condition" box, DvSum will try to select the tables for you and automatically translate the query into graph above.
You might not always see a success message i.e. sometimes DvSum will not be able to translate the join query you have provided into a graph. It does not mean there is a mistake. If you are confident your query is correct, all you need to do is select the tables, write join condition and Validate query.
"Validate" button informs you if the join condition between specified tables is logically correct or not.
Step 3: Fields of all tables involved in Join Condition will show on the left. Select the tables (all or few) which will show in a new single table.
Save the Blend view and you are done. The detail page for this table will open up and now you can run profiler for field values to populate.
Note: MSSQL and ORACLE sources support all joins but SAP source supports only inner and left outer join.