Dave's Adventures in Business Intelligence

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.

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

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.

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.

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

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. 😎