Aug 22 2014

Yes, Virginia, You Can Refresh One Data Provider At A Time

Categories: General,Report Techniques,Web Intelligence Dave Rathbun @ 11:45 am

For a while now I have been whining about the fact that there were features dropped from XI 3.1 during the upgrade to BI4, including how the dimension merging process works. Another gap? In XI 3.1 we had the ability from the query panel to run only a selected data provider, leaving the others alone. When working on complex multi-query documents this could be a big help, especially if some of the data providers had longer refresh times.

A few days ago I was grumbling about this yet again and discovered a way to refresh a single data provider! It’s not perfect (nor was it the most obvious workflow for me) but it does work. I had one data provider that was scanning a huge multi-billion row fact table. To supplement this data I needed to run an additional query against an Excel data provider. I had to make several changes to the Excel file in order to help my data match up correctly, and each time I updated the XLS I had to refresh the entire document in order to see the changes… which was annoying and time consuming. Now I have a solution.

Note: Since I’m talking about joining with Excel, clearly I was using the rich client application. However the same technique outlined below works online with multiple universe data providers as well.

Building Multiple Queries

The process to build a document with more than one data provider is fairly straightforward. I start a new document and select the universe that I need (Motors in this case). While in the query panel I can duplicate an existing data provider (as long as it has been run at least once, which is an odd restriction) or click the Add Query button to include a new data source such as Excel. If I’m on the document display (rather than in the query panel) I can click the Data Access menu and select New Data Provider from the ribbon bar.

New Data Provider

Now suppose I want to refresh just one query? The other day I clicked on the “Data” option on the ribbon bar, shown below:

Data Option

I had not done that in a while and I was poking around and right-clicking looking for something when I noticed that while on that screen I can refresh a selected data provider. That’s great, right?

Well, yes and no. It does allow me to refresh a single data provider, which is fine when I have only changed the data in my supplementary Excel file. If the Excel file has added new columns, then I need to edit my data provider before I refresh. Now I’m back in the query panel where I have to refresh all of the data providers again. Bummer.

Edit Without Refresh

As I mentioned before, this may be old news to some, but I did figure out a trick to solve this. What I did was open the query panel, make the required changes to the data provider, then instead of running the queries (all of them) I used the drop-down menu option for Apply Changes and Close. What does this do? It saves the query definition changes but does not attempt to refresh any of the queries. It also purges any data providers that have been changed to eliminate the old data which might not match the new query definition.

Save and close

If I started from the Data screen, then closing the query panel takes me right back to the same screen, where I can see that the data provider that has been changed has been purged, but my other data provider has been left alone.

Before refresh

Where, as mentioned above, I can now refresh the individual data provider that I just edited. 🙂

Refresh button

When I am done, I have managed to make a change to a single data provider and then refresh only that data provider instead of having to refresh the entire document. It is a multi-step process instead of being able to do everything from the query panel, but using this workflow has saved me hours of development time this week. I figured if I didn’t know about it, it was worth sharing. If I’m the only one who didn’t know about it, please humor me and pretend that this post was useful anyway. 😎

4 Responses to “Yes, Virginia, You Can Refresh One Data Provider At A Time”

  1. Comment by Wale

    My question is have you ever run into error messages importing the excel file into webi? I have some large files which 4.1 can handle but get errors sometimes and sometimes not so it’s not consistent but can’t figure out the cause.

  2. Comment by hong

    curious on the refresh date of Excel file, how come it is 1969 Dec 31?

  3. Comment by Dave Rathbun

    I have, in fact just recently. I was under a bit of a time crunch so I simply exported the XLS as CSV and imported it that way and it worked. 🙂

  4. Comment by Dave Rathbun

    I had not noticed that! I assume it’s because I had not run it yet…