RadarCube ASP.NET Direct: How do I make the Cube work faster?
Posted by Ivan Pashkov on 16 August 2007 04:17 PM
This article applies to:
This article describes how to boost cube performance in your ASP.NET application.
There are four possible reasons why your application may slow down while opening the Cube.
Hence, you may take the following steps to make your application work faster.
Optimize your data source
Whether your relational database is organized effectively or not is very important. Since the data for the Cube belongs to the source data schema or the whole schema, it most likely will contain all the mistakes made while original database was designed. For example, if your database contains duplicated data, the Cube will have to load and process it anyway for optimizing the data structure. Imagine, for example, that the data source for the Cube is a single fact table without any related tables in the schema. This table might look like this:
Here each row of the table will contain duplicated data: CustomerName, CustomerAddress, and CustomerPhone columns. Though the Cube will process such a table without any difficulties, you will certainly loose in traffic and time to load data. Moreover, if you will single out CustomerName as a separate hierarchy in your Cube, the Cube will have to generate the list of all customers and to do lots of comparison operations to make them all unique. Naturally, it can't speed up the whole process.
To avoid such a situation you can help the Cube from the very beginning by designing a proper data schema:
In this case, your will significantly lessen both the traffic and the load time.
Leave out the data that is not really needed in the Cube
The size of the data schema also matters. If it includes many tables like, say, Customers, Shippers, Countries, Regions, Products, Categories, Employees, and so on – it will indoubtedly influence the speed of operation. Still you may not need all these tables in your Cube. However, if you assign a System.Data.DataSet object as the data source, the Cube will take by default all the tables from the whole schema. And even if the Cube does not display some of the tables in the Cube structure, and won't leave any them out. As a result, when the Cube gets opened it will try to read all the tables from the data schema, no matter whether you need them or not. So, organizing the Cube structure is always your responsibility.
Another problem is that you should always remember what fields are used in the Cube. For example, your table of customers may contain lots of information about them, like their addresses, phone numbers, credit card information, birth date and even pictures. Not all of those fields are really needed in your Cube. But, again, the Cube will think you need them for some other purpose, even if they are pictures. So, you should also take care about such extra-fields in your tables, and include in the data schema only what is really needed.
In most cases, it would be a good idea to create a separate DataSet with the only data you really need for your Cube. These simple steps may significantly cut down the size of data to be loaded by the Cube, and so you will get an advantage in speed.
Use the IDataReader or the IDbCommand interfaces
The Cube may read data from the fact table or from a dimension table either with a System.Data.DataTable object or the IDataReader interface. By default, the Cube does as follows: it reads the structure of the dataset and when the Cube is opened, reads the data for building it from a System.Data.DataSet object. That means the DataSet must already be filled with data by the moment the Cube opens. So, before the Cube can start building itself, the whole data must be loaded into the server's memory. This can work when the dataset is relatively small, but with a big one can cause a noticeable lag.
To avoid that problem, the Cube supports reading IDataReader interface. The idea is that you can provide IDataReader interface for any table in the data schema, and if you do, the Cube will use it when it needs to retrieve data. It is quite easy to provide IDataReader interfaces for tables. You can learn more from the following article:
When you provide IDataReader interfaces, you still need to have the dataset because the Cube reads metadata from a System.Data.DataSet object, but you don't have to fill it with data! Instead, you just make the Cube use IDataReader when it needs data. This may boost the Cube performance by several times. So we would strongly recommend you to do that.
Use one multilevel hierarchy instead of several separate hierarchies
When you define the Cube structure, there may be some hierarchies that logically must always go together. For example, such hierarchies as "Year", "Quarter", "Month" or "Categories", "Products" should be logically combined together into multilevel hierarchies so that the Cube interpret them as a unit. It’s not that you’re prohibited to use them as separate hierarchies, but combining such hierarchies into multilevel ones may give you a significant gain in performance. This is because the Cube creates less hierarchy members for a multilevel hierarchy than for several separate ones for the same number of levels. This may be a useful tip, if you have to handle long hierarchies.