12-07-2011 06:38 PM
In our data warehouse, we have a set of tables that contain data summarized from the detail layer. These summary layer tables contain the data we use in our web reports. Currently, we have an information map that includes all 6 fact tables in the summary layer plus all of the relevant dimension tables (about 10). Any given report uses data from only one fact table. We decided to build one info map for all of the summary fact tables to simplify the maintenance of the info map rather than building one info map for each and every fact table.
My question is.... Are they any significant performance penalties for having a large info map for all 6 fact tables when each web report uses data from a single fact table? Or alternatively, would we see performance improvements if we had separate info maps for each and every fact table?
I'm curious to know if anybody has had the opportunity to try these different approaches?
12-07-2011 07:42 PM
Performance for a map with many tables will not be as good as performance for a map with one or two tables. In addition, if you create an information map with hundreds or thousands of data items, then the performance is going to be poor. Information maps are intended to provide an easy interface to a specific subset of data to non-technical end users. So, typically, you would not simply create one information map that includes all columns from a data source. For instance, a report creator will likely have a difficult time trying to navigate through 2000 data items to find just those data items that are useful for one report, even if the data items are in separate folders. Instead, SAS technical support recommends creating multiple information maps, each containing a selected number of columns that serve a particular reporting need.
12-07-2011 08:16 PM
Thanks for your response.
My question dealt with the performance issue specifically because usability has not been an issue. We have a small group of "super users" who we trained on the underlying data model, pluse we put a lot of effort into structuring the info map with folders, meaningful field names, predefined filters, etc. Our users also received training on using the info maps to develop their own reports with Web Report studio. The users do this all the time with great success.
However, our users sometimes find that the web reports come up too slowly. So we want to understand the potential for performance improvements if we break up our "super" info map into several smaller more focused info maps. Do you have any performance metrics you can share with me?
FYI the info map structure, here is a screen shot which shows the highest level folders which makes it easy for our users to navigate to find the table they need and then the specific data items...
12-08-2011 05:03 PM
Actually, let me clarify. The performance hit when the map contains a lot of data items typically occurs when the Available Data Items list is loading for the report authors. For example, we've seen it take 30 seconds to load 1400 data items.
The report itself, which, I assume, will contain only a small subset of the available data items, should not typically experience poor performance due to map size.
To see what is happening with the query behind the scenes, you can turn on logging for the pooled workspace server. For instructions, see http://support.sas.com/kb/34/567.html
I would also recommend reviewing the performance tips in this SAS note: http://support.sas.com/kb/39/065.html
Note that we don't have any official performance metrics because there are so many elements that can affect performance, even the browser.