OLAP Reports using C# in .net application

If you are familiar with BI tool and OLAP system, you know that it is inefficient to perform same join operation multiple times for different reports which use same joining. Also, if same report is visited by many users then loading same data in different memory location is also inefficient. For this reason, BI tools are using OLAP cube & universe. Here I will show you, how can you do this by using singleton pattern in C# & .net framework.

The main logic behind this is to create a singleton class which loads the data of universe/cube just once. So, different session & different format of a set of data will read the database just once and keep them in memory. Then same dataset will be used among all sessions and similar reports. Yes, it requires large memory allocation, but it is highly scalable. If you have to do this type of reports and you are not doing this, your BI reporting looses scalability surely. Here is the sample code,

namespace WebSingleton
{
public sealed class LoadUniverse
{
// Static members are 'eagerly initialized', that is,
// immediately when class is loaded for the first time.
// .NET guarantees thread safety for static initialization
private static readonly LoadUniverse _instance = new LoadUniverse();

private DataSet _universe;

// Note: constructor is 'private'
private LoadUniverse()
{
//
// TODO: Init universe data by loading different datatable of dataset
//using joining operation
//
}

public static LoadUniverse Instance
{ get { return _instance; } }

public DataSet Universe
{ get { return _universe; } }

}
}

So, when you design reports, you should call the "Universe" property of the object of the singleton class. As it is singleton, in all modules of your reporting application same object reference is used and the private constructor will be called just once. To load the the datatable of dataset, you should use complex query which joins your fact and dimension tables. This will help you a lot if your database is designed for operational (OLTP) system but you have to perform reporting (OLAP) on same database. Now, from the datatable you can load your own generic list List and use LINQ to run the query on your universe/OLAP cube if you need further filter or aggregation.

Another thing I should mention which is if I don't construct forcefully at the starting of application, the object will be lazily constructed. Thus, the first report viewer sees the first time report loading very slow. So, you can create it in global.asax in following manner -

void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
WebSingleton.LoadUniverse loadUniverse = WebSingleton.LoadUniverse.Instance;
}

Some developers may use static class instead of singleton pattern. But it should not be used. http://www.dotnetperls.com/ discusses the comparison between these two nicely.