Loading Profile...
I have two Excel input files. One is Accounts and one is Contacts. I would like to join these two tables together before performing the profile so that I can roll-up the results by Account Type.
More specifically, I would like to profile the Contact data, however, Accounts contains important information such as Account_Type that I would like to use to roll-up my Contact results.
In SQL I would create a SQL query and join the tables by the common foreign_key (eg. contacts.account_id = accounts.account_id). The query would then include the necessary fields from Contacts and those fields from Account that I wish to include in the "de-normalized" table.
Is this possible or must it be peformed beforehand?
Help get this topic noticed by sharing it on
Twitter,
Facebook, or email.
Twitter,
Facebook, or email.
Is it possible to join to tables before profiling?
-
Hi
Normally it would not be possible in DQA tool.
But since You have data in excel files, there is little workaround.
You can try to use winows ODBC interface to communicate with Your excel files as they were database tables and then join them in sql. For this, you need exvel file in 2007 format (xslx) and both your tables in the file (each on its own sheet)
First, You have to configure odbc through odbc administrator. Please note that You have to use ODBC administrator (odbcad32.exe) from your winsows/system32 folder (there is similar file in system64, which will not work).
There You can add datasource, select microsoft excel driver (version 12) and finally map your excel file to it.
Next you add this datasource as database with ODBC driver in DQA. Then you can treat each sheet as single table and join them in JDBC reader.
Please note that excel files mounted this way are very sensitive for access. They only allow single connection. So You have to close excel files and disconnect from database before running the plan. If you run into "cannot connect to the database" issue, try restarting dqa and then run the plan without first connecting to excel datasource (plan will make connection automatically).Comment -
-
Hello,
I have an additional clarification. In DQ Analyzer we expect users profile a single table or multiple tables without a need to join the data or Join them in SQL Query within JDBC Reader. In our main DQ Tool Data Quality Center (tutorial videos) is a possibility to join the data either using a Dictionary file or use the step Join to visually join the flows from the input files or other sources.
-
EMPLOYEE
EMPLOYEE
