RadarCube Windows Forms Desktop: How to make the cube work faster?
Posted by Ivan Pashkov on 27 August 2007 04:05 PM
This article applies to:
This article describes how to boost cube performance in your ASP.NET application.
There may be four main reasons why your application may slow down while opening the cube.
Here are several points you can try out to make your application work faster.
Optimize your data source
This point has to do with how effectively your relational data source is organized. Because the data for the cube a part of the source data schema or the whole schema, it most likely will contain all mistakes made during original database design. For example, if your database contains duplicating data, the cube will have to load and process this anyway doing its job for optimizing the data structure. Assume 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 duplicating data which is CustomerName, CustomerAddress, and CustomerPhone columns. Though the cube will process such a table with no problem at all, you will certainly loose in traffic size and time to load data. Moreover, if you want to have CustomerName as a hierarchy in your cube, the cube will have to make the list of all customers and it will have to do lots of comparison operations to make them all unique. It can't speed up the whole process.
To avoid the situation you can help the cube from the very beginning making the proper data schema:
In this case your traffic size will be much less, and you will spare the load time.
Leave out the data that is not really needed in the cube
The source data schema may be very big. It may include many tables like Customers, Shippers, Countries, Regions, Products, Categories, Employees, and so on. Still you may not need all these tables in your cube. However, if you use System.Data.DataSet object as a data source, the cube will take by default all tables from the whole schema. And even if the cube will not show some tables in the cube structure, it will think you need those table for another purpose (for example to compute calculated fields in the fact table), and won't leave the unused tables out. As a result, when the cube gets opened it will try to read all tables from the data schema, and it may read unneeded tables as well. So it is always you that is responsible for the data source structure for the cube.
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 the customers, 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 another purpose, even if it's pictures. So you should also take concern about extra-fields in your tables, and include in the data schema only really useful fields.
In most cases you will want to create a separate DataSet for your cube that will hold only the data that is needed. These simple steps may significantly cut down the size of data to load by the cube, and so you will get incredible speed advantage.
Make use of IDataReader or IDbCommand interfaces
There are two ways the cube may read data from the fact table or from a dimension table. It may use either System.Data.DataTable object or IDataReader interface. By default the cube does as follows. It reads the structure of the dataset and when the cube gets opened, reads the data from the System.Data.DataSet object to build the cube. That means the DataSet must already be filled with data at the moment the cube opens. So before the cube can start building itself, the whole piece of data must be loaded into server's memory. This can work when the dataset is relatively small, but will make a big problem with the big dataset.
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 the tables. You can know more from the following article:
When you provide IDataReader interfaces, you still need to have the dataset because the cube takes metadata from System.Data.DataSet object, but you don't need to fill it with data! Instead you just tell the cube to use IDataReader when it needs data. This may boost the cube performance by several times. So it is strongly recommended to do so.
Use one multilevel hierarchy instead of several segregated hierarchies
When you define the cube structure, you may have some hierarchies that logically must always go together. For example, such hierarchies like "Year", "Quarter", "Month" or "Categories", "Products" should be logically combined together into multilevel hierarchies so that the cube considers them as a unit. Still you're not prohibited from using them as segregated hierarchies. If you have such hierarchies, using multilevel hierarchies instead of several segregated hierarchies, may be of significant gain in performance. This is because the cube makes much less hierarchy members for a multilevel hierarchy than for several segregated ones for the same number of levels. And this may be of great use especially if your hierarchies are long enough.