Is it possible to upload two QVWs (from unique sources) into one QVW via a BINARY Load?
Started with data from two excel files with two sheets each that I concatenated successfully.. stored each concatenation as two separate tables...the below follows the path I took for ONE excel file and ONE concatenation....
I can perform a BINARY load successfully if I do one Table at a time, but I need data from both tables in my QVD. Here's my path so far - help is greatly appreciated
CAPFactTable: LOAD* FROM (ooxml, embeddedlabels, tableis [Feb 28 CAPs]);
LOAD* FROM (ooxml, embeddedlabels, tableis [Mar 28 CAPs]);
I then stored each table as a qvd....
STORE CAPFactTable into O:\Report QV automation\04_QVD_Files\CAPFactTable.qvd(qvd);
Which then allowed me to load each table separately....
My problem is that this table, 'CAPFactTable' only contains data from one source. I also want to upload 'IssueFactTable' that I have created in a parallel fashion in a BINARY upload within the SAME QVD, even though they are from unique excel files.
Does this make sense? The help is greatly appreciated!
Re: Is it possible to upload two QVWs (from unique sources) into one QVW via a BINARY Load?
As Rob pointed out - you can only do one binary load in your script, and it has to be the first statement.
The binary load basically takes everything from one existing .qvw file and imports it into another .qvw file. So now you're 'stuck', as you can not run a second binary load from a second 'source' qvw file into your target .qvw.
So, after the first binary load, you could possibly then continue loading more data by loading additional .qvd data files and merging them into the data model that you've got in your target .qvw file. (This allows you to skip at least one .qvd file load.) Sort of clumsy - as this assumes that you're hitting a specific source QVW file, and then doing additional loads from QVD files.
You might try this:
Create extract qvw files with scripts that pull data from your different excel files or other data sources, and then write those sources to .qvd data files. This is something like what you've done with your two different .qvw files right now, except you can use one single extract script, with different tabs to pull from different data sources, and then writing the extracted data into the binary .qvd data format which can be used in multiple places.
Next, build a "data model" .qvw file, with scripts that load data from 1 to many .qvd data files, and merge the data into your common table. You may be doing additional manipulation here (or in the initial extract scripts) to make sure that the data fields all match.
Once your data model .qvw is done doing all the data loading, merging, and transformations, you could then do a binary load into your final dashboard .qvw file, where you have all of your presentation setup (ie, the user .qvw file).
This tiered approach lets you merge multiple data sources into a common data set, do any transformations or manipulations on the input data, and create a coherent data set on the 'back end', without impacting anyone using the front end user dashboard. Once all the heavy work is done on the backend, you can then do a quick binary load to update your dashboard.