on 01-16-2012 2:10 PM
Hi,
I need to extract some data from several tables of a sql server database.
So sender jdbc channel must pick up records from a header table, a detail table and more than one sub-detail tables.
I hope you experts can help me to find the best way to do it, considering:
data integrity between all tables
Avoid to repeat data (with a multiple select) , so there is a great volume of information.
Thanks,
Carme.
Hi,
As per my knowledge, it is not possible to select data from several related SQL tables without having data repetetions. Even using joins will cause data redundancy (even bigger, if you ask me). Higher level data will be repeated for each detailed record. Data being redundant to some extent is actually indispensable for you to correctly match header, detail, subdetail and detal records in PI.
The only alternative I can see (when using a pure database as a sender system) is consuming a web service whose server will be your PI system. As far as I know this is possible with some DBMS. Thus you can avoid data redundancy by structurizing your XML data correctly. But I am not sure if this is the best idea. First of all, instead of redundant data you will get the XML overhead. Moreover - it is PI's duty to change the data format from whatever the source is to whatever the output is required to be.
Hope this helps,
Greg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, Gregorz for your suggestion.
Indeed, I'm analiizing to do it with a stored procedure and extract data in a XML format (XMLSQL) .
Anyone experience with that? Is it possible with JDBC adapter?
Thanks, in advance,
Carme
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
As far as I understood, you want to ensure that the data already sent is not sent again, is that correct? If so, then you have to ensure it on the database level. For instance, you might create a stored procedure (like Michal has suggested) that not only will collect data and return it to PI, but also mark the data as already sent. For instance you can set some indicator or even better: set a "distribution timestamp" (might appear useful for debugging reasons some day).
Hope this helps,
Greg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gregor,
I know that it's necessary mark the data already uploaded to ensure that it is not sent again.
Perhaps i have not explained correctly my doubt.
I must upload our centrer's budgets but the data is distributed in multiple sql tables:
Header table --> with key center and key budget and another header data
Detail table --> with key center, key budget , key number line and another detail data (1 header --> N detail)
Sub-detail table --> with key center, key budget, key number, key number line , key promotion number and another sub-detail data ( 1 detal record --> M sub-detail records)
My doubt...
Doing a select with joins of all tables, many fields of header and detail tables will be repeated .
How can i extract all budget data (entirely and with integrity ) without repeat key fields (center, budget, key number) and without repeat header fields?
Regards,
Carme.
Hi,
As per my understanding of your problem, Data fetched from database will be unique one for Header, and detail key combination from sub detail which will contain Primary key of header table, foreign key of Detail table, with combination of these keys, sub detail will return unique value for all records found in Header table.
And you have to use all join SQL query in one Stored Procedure to fetch it using JDBC adapter and mark it if it is necessary.
Regards
Aashish Sinha
Hi,
create a stored procedure in your DB and call it from PI - JDBC sender adapter
Regards,
Michal Krawczyk
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.