Introduction

Most companies have large amounts of data that the various business areas have a need to process and analyze. Often times the data is not in a useable format or does not have the right view for the business managers to make use of it. For example each business manager may have his own different requirements as to how and what data he wants to see.

Through the use of cubes this data can take on the different views that are required for the different business areas. The users will also require a good user interface to enable them to better understand the data and an interactive way to create cubes to view this data. One common way to present this is through the use of a web application that allows the users to create their own analysis cubes with minimum configuration on their system.

Cubes contain data that is extracted from database tables and processed using aggregations. SQL Analysis Services is the software that comes along with SQL Server that provides this functionality. Once Analysis Services is installed you are able to create your own measures, dimensions and cubes and can process the cubes using SQL Analysis Manager with data from SQL Server or Access. However, SQL Analysis Manager does not in itself have any built in facility to create cubes in web applications. In this article I am going to develop a solution that creates cubes through a web application using ASP.NET and C#.

The article will start with an overview of some common terminology used throughout the article. This is followed by an overview of the DSO classes that will be used in the creation of the sample. Lastly I will go over the creation of the Database, DataSource, Dimension, Measure and Cube objects using C#.

System Requirements

To run the code for the sample you should have:

• Window XP or Window 2000 Server


• IIS 5.0


• .NET Framework 1.x


• VS .NET 2003


• SQL Server 2000


• SQL Analysis Server 2000 with Service Pack 3


• Microsoft Access Database

Sample Code

The sample code contains a VS.NET 2003 solution with 2 projects:

CubeAnalysisLibrary is a class library project which includes the functionality for implementing the following tasks:

• Creation of a Database, DataSource, Dimension, Measure and Cube objects in SQL Analysis Server


• Providing a list of Databases, DataSources and Cubes from the SQL Analysis Server


• Providing a list of Dimensions and Measures for a particular Cube

The WebCubeUIPages project is an ASP.NET web application project that contains all the user interface pages for all of the tasks above.

To install this sample, save the downloaded .zip file to the C:Articles directory and extract the files to the same directory. Make the WebCubeUIPages directory an IIS virtual directory. Next open the default page in Internet Explorer by using the following URL http://localhost/webcubewizard/default.aspx.

Terminology

Let’ me start by providing some useful terminology to understand the basic concepts presented throughout the rest of this article:

Analysis Server – This is the server for holding the analysis information. This server contains databases, data sources, dimensions and measures.

Database – The database is the container for a data sources, dimension, measures and cubes. The database is the immediate sub level to the analysis server. Creating a database is the first step in creating an analysis cube.

DataSource – A data source is the immediate sublevel to the database. A data source contains the information required to connect to a relational database. The analysis server will fetch the metadata and data from during the processing of the cube using the data source connection string. The analysis server supports SQL Server, Access and Oracle.

Dimension – Dimensions are the attributes of the cube. It enables the cube data to be displayed in multiple directions. The dimensions can be either private or shared. Private dimensions apply only to a particular cube whereas shared dimensions can be used for multiple cubes.

Level – Levels are the columns of the dimension table. The levels break down the dimension data into sub levels.

Measure – A measure is numeric data and also columns from a fact table. Some examples of a measure are store sales, store cost and number of customers.

Fact table – Fact tables are relational tables in a data warehouse. From these the measures (table columns) can be used to calculate aggregations for a multi dimensional cube. A fact table also has a foreign key relationship with the dimension tables.

Aggregations – Aggregations are the summarization of information using aggregate SQL functions like sum, average etc.

Cube – A cube is a set of multi-dimensional data. A cube contains dimensions and their levels, measures, data sources, roles, and commands. It puts together most all the other analysis server components.

SubClassTypes – A subclasstype represents the type of an object. If you take the cube object, you can specify it as either sbclsLinked (linked cube) or sbclsVirtual (virtual cube) or sbclsRegular (regular cube).

The DSO Object Model

Figure 1 shows a simplified graphical view of the DSO object model. This object model uses the DSO namespace to interact with the classes that are used throughout this article.

Figure 1. Simplified view of the DSO object model

The DSO is the COM component that is used to create analysis objects. The DSO component provides a library of classes and interfaces to connect to and access the data from the analysis server. These classes and interfaces are arranged in a hierarchy in the form of layers (see Figure 1). In this article I am going to discuss in detail how to create an analysis cube. In most of the code snippets of this article you will see MDStores, which is a collection of either databases or cubes.

If you want to create your own analysis application using VS.NET, you need to add the DSO component as a reference to your project. The DSO component is an unmanaged COM component. .NET managed applications do not directly expose and interact with COM components but you can interact with COM components in one of two ways:

1. You can right click on references in the Solution Explorer, select add reference, select the COM tab, and then select the Microsoft Decision Support Object 5.1 component from the list that appears, and click the Ok button.


2. You can also do basically the same thing by creating a Runtime Callable Wrapper (RCW) using TLBImp tool. This tool will create the metadata so .NET can access the DSO component.

In the following sections I will provide an overview of the properties and methods of the classes I am going to use throughout this article.

Server Class

The DSO.Server class is the main class in the DSO component. It contains functionality to hold a collection of Database objects (MDStores) and to open and close the analysis service connections. The following table describes some of the various properties and methods, which I will use in this article. Note that in this and subsequent tables, the methods are dstinguished from the properties by brackets after the method name:

Property/Method	Description
State	Returns the current connection state
ServiceState	Returns the execution state of the analysis services
Name	Returns the name of the analysis services
Connect()	Connects to the analysis services
CloseServer()	Disconnects from the analysis services
ConnectTimeout	The property to assign the timeout value in seconds
MDStores	Returns a collection of Databases
Description	Assigns a description of the analysis services

Database Class

DSO.Database is the class used to create Database objects. The Database object is the container for storing Cubes, DataSources and Dimensions. The following table describes various properties and methods, which you’ll use in this article.

Property/Method	Description
Name	Holds the database name
Description	Holds the description of the database
MDStores	Returns the collection of cubes
Update()	Used to update information to the database within the analysis services
DataSources	Returns the collection of data sources
Dimensions	Returns the collection of dimensions

DataSource Class

DSO.DataSource is the class for creating DataSource objects. This object holds the link to a relational database (SQL Server, Access, Oracle, Sybase etc). The following table describes various properties and methods used in this article:

Property/Method	Description
Name	Holds the data source name
Description	Holds the description of the data source
ConnectionString	Holds the connection string to a relational database
Update()	Used to update the information in the data source within the analysis services
Dimensions	Returns the collection of dimensions

Dimension Class

DSO.Dimension is the class used to create Dimension objects. Dimension is the collection of Levels. Each Level points to a column in the relational database table. As usual, the following table describes various properties and methods, you’ll encounter in this article:

Property/Method	Description
Name	Holds the dimension name
Description	Holds the description of the dimension
DataSource	Holds the data source
Levels	Returns a collection of levels
FromClause	Used to assign the relational table name
Update()	Used to update the dimension stored in the analysis services
Process()	Used to process a dimension on the analysis services

Level Class

DSO.Level is the class used to create Level objects. Each Level points to a column of a relational database table. The following table describes various properties and methods, which I will use in this article:

Property/Method	Description
Name	Holds the level name
Description	Holds the description of the level
MemberKeyColumn	Holds the table column name
ColumnSize	Holds the size of the column in bytes
ColumnType	Holds the data type of the column
EstimatedSize	The estimated number of members in the level object

Cube Class

DSO.Cube is the class used to create Cube objects. This object contains the information from a DataSource, Dimensions and Measures to the create Cube. Using this object you need to select dimensional tables, fact tables and what type of analysis cube you are going to create. Finally you need to process the Cube to extract data from relational database tables and do aggregations for each Dimension and Level. The following table describes various properties and methods, which we will use in this article:

Property/Method	Description
Name	Holds the cube name
Description	Holds a description of the cube
DataSources	Returns a collection of data sources
SourceTable	The fact table for the cube
EstimatedRows	Approximate number of rows in the fact table
Dimensions	Returns the collection of dimensions
Measures	Returns the collection of measures
JoinClause	Used to set the join of the fact table columns with all dimensions column
Update()	Used to update information to the cube
OlapMode	The mode of the cube (MOLAP, ROLAP, HOLAP)
Process()	Used to process the cube
IsValid	Checks the validity of the cube

Measure Class

DSO.Measure is the class used to create a Measure object. A Measure is a fact table column. In the Measure object you need to specify what type of aggregation you are going to use. The table below provides the common properties for a Measure:

Property/Method	Description
Name	Holds the measure name
Description	Holds a description of the measure
SourceColumn	Holds the column name of the fact table for the measure
SourceColumnType	Holds the column type of the fact table
AggregateFunction	Holds the type of aggregation (sum, average, count, min, max etc)

Creation of Analysis Cube using User Interface Pages and Library Functions

I have discussed many of the basic objects used in the SQL Analysis Services. Now I will demonstrate their use by creating an analysis cube for a sales application that uses these basic objects. The cube will allow the user to query the sales for particular country, particular store or during a selected promotional period.

I am going to be creating a Database, DataSource, Dimension, Measure and Cube object using C# code. The list below provides a high level overview of the steps I will be following to accomplish building the sample application:

1. Connecting to the Analysis Server


2. Creating a Database


3. Creating a DataSource


4. Creating a Dimension


5. Creating a Cube


6. Creating a Measure


7. Processing a Cube


8. Creating a Virtual Cube


9. Viewing the Cube Data

The sections that follow are a step by step walkthrough of above tasks to create a normal and a virtual cube. Most of code snippets below here are from the CubeAnalysisLibrary project.

1. Connecting to the Analysis Server

The first step in creating a Cube is creating a DSO object and connecting to the analysis server. Before writing any code, a developer has to add the DSO assembly as a reference to the CubeAnalysisLibrary project. I create the DSO object by creating an instance of the ServerClass class from the DSO namespace. Next I check the status of server connection before connecting to the analysis server. If the connection state is not connected, I connect to the server by calling the Connect() method of the DSO object and pass the server name as a parameter. Lastly I assign a description and connection time out using the appropriate properties of the DSO object and lastly update the information to the analysis object.

The code below shows how this looks in the sample application:

//Connecting to the Analysis Server

DSO.Server objDSOServer = new DSO.ServerClass();

public CubeAnalysisLibraryClass(string strServerName)

{
  if(DSO.ServerStates.stateConnected != objDSOServer.State)
  {
    objDSOServer.Connect(strServerName);
    objDSOServer.Description = "Analysis server for creating ASPToday 
      Business Cubes";
    objDSOServer.ConnectTimeout = 20000;
    objDSOServer.Update();
  }

}

2. Creating a Database

Next I am going to create a new Database. This Database is different from a relational database,and is used within the analysis server. This is the container for Dimensions, Measures and Cubes. Figure 2 shows the user interface for creating a Database.

Figure 2. The user interface for creating a database

CreateDatabase() is the method in the CubeAnalysisLibrary class required to create a Database object. I need to create the Database object by calling the MDStotes.Addnew() method of the DSO object and passing a Database name and sub class type object as parameters. Next I assign a description of the database to the Database object. Finally I update the Database object to update the description to the analysis server. After creating the database you can go to Start → All Programs → Microsoft SQL Server → Analysis Server and select Analysis Services to find the newly created database.

// CreateDatabase Library Function

bool blValue = objDSOServer.MDStores.Find(strAnalysisDatabaseName);

if(blValue  false)
{
  DSO.Database objDatabase =
    (DSO.Database)objDSOServer.MDStores.AddNew(strAnalysisDatabaseName, 
    DSO.SubClassTypes.sbclsRegular);
  objDatabase.Description = strDatabaseDescription;
  objDatabase.Update();
  return "Database Created Successfully";				
}
return "Database already exists in the server " + strAnalysisServer;

3. Creating a DataSource

After creating a Database I need to create the DataSource and assign the database connection to the DataSource. Figure 3 shows the user interface for creating the data source.

Figure 3. The user Interface for creating a DataSource

In this article I am using the FoodMart 2000 MS Access database; this comes along with SQL Analysis Server. You can find this database by default at C:Program FilesMicrosoft Analysis ServicesSamplesfoodmart 2000.

Here Server Name, Database Name, Connection String and Datasource Name are the input parameters required to create the DataSource. The Database name is the name of the SQL analysis database I am going to use to create the Cube. The connection string is an input parameter required to connect to the relational database. In the next sections I'll identify what tables are required to create the Cube from the relational database.

In the Create Datasource user interface you should enter the server name in Server Name text and click the Go button to load the available databases from the selected server in to the Database Name drop down list (You can find the code snippet in this article to get data bases from analysis server in from List Databases section). Now you need to select the database, so enter the Connection String to connect to required SQL analysis server and the Datasource Name and click the Create DataSource button to create a DataSource. See the code snippet below to see the code to create a DataSource.

//Create DataSource Library Function
DSO.MDStore objDatabase;
DSO.DataSource objDatasource;
objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);
DSO.SubClassTypes objClassTypes = new SubClassTypes();
objDatasource = (DSO.DataSource)objDatabase.DataSources.AddNew(
  strDatasource, objClassTypes);
objDatasource.ConnectionString = strConnectionString;
objDatasource.Update();
objDSOServer.CloseServer();
return strDatasource + " datasource is created successfully";

I start by retrieving the Database object by calling the MDStores.Item() function of the DSO object. Then I create a DataSource object by calling the DataSources.AddNew() method of the Database object passing the DataSource name and SubClassType. Lastly, I assign the connection string to the DataSource object and update the DataSource object. Now I should be able to see the newly created DataSource in SQL analysis manager.

4. Creating a Dimension

The next step after creating the DataSource is to create the Dimensions. A Dimension is a collection of Levels from one table. A Level points to one of the columns in a table. First I need to decide what tables I am going to use to create the Dimensions from the DataSource created earlier. Similarly I need to decide what the columns required from the selected table to create the Levels. Figure 4 shows the user interface for creating the dimension.

y

Figure 4. The user interface for creating a dimension

Here Server Name, Database Name, Datasource, Tables and Column are the input parameters to the user interface. Start by entering the server name in the Server Name text box and press the Go button to retrieve the databases into the Database drop down list. Select the Database which was created in the previous section. Now you'll get data sources populated in the Datasource drop down list (See the Listing DataSources section of this article to get data sources from analysis database).

Select the data source which you created above in the previous section from the Datasource drop down. Now it loads the tables from the selected DataSource into the Tables drop down list. Select the table to load the table columns into the Column drop down list. Type the level name in the Level Name text box, select the columns data type in Column Type drop down list, enter the column size in Column Size text box and press the Add Level button to add the level to the Levels list box. The Column Type drop down list contains only analysis server data types. These data types are internally mapped to relational database data types. I repeat the same procedure to add all the levels I need. Once I create all the levels I enter the dimension name and press the Create Dimension button to create the dimension in the selected database. See the user interface code snippet below used to create a Dimension for details.

//User Interface Code
DimensionLevel[] objDimensionsLevels =
  new DimensionLevel[ddLevels.Items.Count];
for(int item=0; item<ddLevels.Items.Count; item++)
{
  ListItem objLtItem = ddLevels.Items[item];
  string strValue = objLtItem.Value;
  char[] sep = new char[1];
  sep[0] = ',';
  string[] strValues = strValue.Split(sep);
  objDimensionsLevels[item] = new DimensionLevel();
  objDimensionsLevels[item].strColumnName = strValues[0];
  objDimensionsLevels[item].intColumnSize = Convert.ToInt16(strValues[2]);
  objDimensionsLevels[item].intColumnType = Convert.ToInt16(strValues[1]);
  objDimensionsLevels[item].intEstimatedSize = 1;
  objDimensionsLevels[item].strLevelName = objLtItem.Text.Trim();
}
objDataWrho = new CubeAnalysisLibraryClass(txtServerName.Text.Trim());
lblMessageEx.Text = 
  objDataWrho.CreateDimension(ddDatabases.SelectedItem.Text.Trim(), 
  ddDatasources.SelectedItem.Text.Trim(), ddTables.SelectedItem.Text,  
  txtDimensionName.Text, objDimensionsLevels);

You can find the DimensionLevel class inside the CubeAnalysisLibraryClass.cs file. The DimensionLevel class has a property collection containing the column name, column size, column type, estimated size and level name. Next I call the CreateDimension() function of the DSO object to create the Dimension object. The next section discusses how to create a Dimension object.

//CreateDimension Library Function
DSO.MDStore objDatabase;
DSO.DataSource objDatasource;
DSO.Dimension objDimension;
DSO.Level objLevel;
DSO.SubClassTypes objClassTypes = new SubClassTypes();
objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);
objDatasource = (DSO.DataSource)objDatabase.DataSources.Item(strDatasource);
objDimension = DSO.Dimension)objDatabase.Dimensions.AddNew(
  strDimensionName, objClassTypes);
objDimension.DataSource = objDatasource; //Dimension data source
objDimension.FromClause = strTableName.ToLower(); //Related table
objDimension.JoinClause = ""; //Used in snowflake schema
foreach(DimensionLevel objectLevel in objLevels)
{
  objLevel = (DSO.Level)objDimension.Levels.AddNew(
    objectLevel.strLevelName, objClassTypes);
  string strMbrKyColm = """ + strTableName.ToLower() + ""."" + 
    objectLevel.strColumnName.ToLower() + """;
    objLevel.MemberKeyColumn = strMbrKyColm;
    objLevel.ColumnSize = objectLevel.intColumnSize;  //Column data size in 
    // bytes
  objLevel.ColumnType = objectLevel.intColumnType; //Column data type
  objLevel.EstimatedSize = objectLevel.intEstimatedSize;
    // Distinct members in column
}
//Update the product dimension
objDimension.Update();
try
{
  objDimension.Process(DSO.ProcessTypes.processDefault);
}
catch(Exception ex)
{
  return ex.Message;
}
return "Dimension created";

I start by creating a Database and DataSource object using the objDSOServer.MDStores.Item() and objDatabase.DataSources.Item() methods by passing a Database and DataSource name. Then I create the Dimension object by calling the Dimensions.AddNew() method of the Database object, passing the dimension name and SubClassTypes object as parameters. Next I assign values to the DataSource, FromClause and JoinClause properties of the Dimension object. Now I need to add the Level objects to the Dimension object. The Level object is created by calling the Levels.AddNew() method of the Dimension object and passing the Level name and SubClassTypes object as parameters. Now I assign values to the MemberKeyColumn, ColumnSize, ColumnType and EstimatedSize properties of the Level object. I repeat the same procedure for adding all the Level objects to the Dimension object. The EstimatedSize is the approximate number of member in the Level object. For example All Level will always be one member. Next I update the Dimension object by calling the Update() method of the Dimension object to make the updates occur on the analysis server. Finally I call the Process() method of the updated Dimension object to process the Dimension by passing DSO.ProcessTypes.processDefault as the input parameter to the function.

5. Creating a Cube

So far you've created a Database, DataSource and Dimensions. These are the main inputs required to create the Cube. Now you're going to discuss how to create a Cube using fact tables, Dimensions and Measures. The user interface for creating a Cube object can be seen in Figure 5.

Figure 5. The user interface for creating the cube

Before showing how to create a cube I will give a brief explanation about the storage models. You can store the raw data and aggregated data of the cube in different storage formats. Multi dimensional OALP (MOLAP) stores aggregated data and fact table data on the analysis server in a multi dimensional format. Relational OLAP (ROLAP) stores the fact table data and aggregated data in the relational database. HOLAP is the combination of ROLAP and MOLAP. In HOLAP the fact table data will be in a relational database and aggregated data will be stored in an analysis database.

Now we can create the Cube object using the already created DataSource, Dimensions and Measures along with fact table and the storage model information. First you need to select the type of Cube you want to create and enter the analysis server name. Then press the Go button to list the databases existing on the server to the Database drop down list. Next select the Database to get the list of DataSources to the Datasource drop down list. Select the Datasource to list the Fact Tables and Dimensions (See the Listing the Dimensions and Listing the Tables sections of this article for information on how to retrieve the dimensions and tables to the user interface). Select the Fact Table to get the list of Measures. You need to select which of the Measure columns need to added to the cube. Now select the type of Storage Model, enter a Cube Name and press the Create button to create the cube. The button click has the functionally to create the Cube, adding Measures to the cube and the processing of the cube. Here we will discuss how to create Cube object.

//Create Analysis Cube Library Function
DSO.MDStore objDatabase;
DSO.Cube objCube;
objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase); 
DSO.SubClassTypes objClassTypes = new SubClassTypes();
objCube = (DSO.Cube)objDatabase.MDStores.AddNew(strCubeName, objClassTypes);
objCube.DataSources.AddNew(strDatasource, objClassTypes);
objCube.SourceTable =""" + strFactTable + """;
objCube.EstimatedRows = 100000;
for(int intList =0; intList<strDimensions.Length; intList++)
{
  strDimensions[intList]=strDimensions[intList].Replace(""","");
  objCube.Dimensions.AddNew(strDimensions[intList], objClassTypes);
}
objCube.JoinClause = strJoin;
objCube.Update();
return strCubeName + " is created";

You have to create the Cube by using the created Database, Datasource and Dimension objects. Create the Database and DataSource object by using the input function parameters. Then I call the MDStores.AddNew() method from the Database object to get the Cube object. Next you need to assign the DataSource to the Cube by calling the DataSources.AddNew() method from the Cube object. Now you should assign the fact table name to the SourceTable property, estimated number of rows to the EstimatedRows property.

The cube has to understand the relationship between the fact table and the dimension tables. You need to create the joins between the fact table and the dimension table and assign the join to the JoinClause property of the Cube object. See the code below for an example of the join which I have used for creating the Cube for this article.

strJoin = "("sales_fact_1997"."product_id"="product"."product_id")";
strJoin += " and ("sales_fact_1997"."store_id"="store"."store_id")";
strJoin += " and
  ("sales_fact_1997"."promotion_id"="promotion"."promotion_id")";

Finally I call the Update() method of the Cube object to update all the values of the Dimensions and Measures within the Cube.

6. Creating a Measure

Next you need to create a Measure and add it to the Cube you just created. Measures are the numerical columns of a fact table (That's the main table used to extract numerical data for creating a cube). This section will describe how to create a measure and how to add it to a cube.

The code snippet below is used in the sample to create a measure. First I get the Measure object by calling the Measures.AddNew() method of the Cube object. I then assign the source column to the SourceColumn property of the Measure object. The SourceColumn needs to be assigned along with the fact table name in the format as shown in the code. Then I assign the column type to the SourceColumnType property of the Measure object. Next I assign the type of aggregation to the AggregateFunction property of the Measure object. Finally I call the Update() method of the Cube object to update the Measure to the Cube object.

//Create Measure method
DSO.MDStore objDatabase;
DSO.Cube objCube;
DSO.Measure objMeasure;
DSO.SubClassTypes objClassTypes = new SubClassTypes();
objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);
objCube = (DSO.Cube)objDatabase.MDStores.Item(strCubeName);
char[] sep = new char[1];
sep[0] = ',';
try
{
  for(int intList = 0; intList<strMeasures.Length; intList++)
  {
    objMeasure = (DSO.Measure)objCube.Measures.AddNew(
      strMeasures[intList],objClassTypes);
    objMeasure.SourceColumn = """ + strFactTable + ""."" + 
      strMeasures[intList] + """;
    objMeasure.SourceColumnType = 2;
    objMeasure.AggregateFunction = DSO.AggregatesTypes.aggSum;
  }
  objCube.Update();
}
catch(Exception exMsg)
{
  return exMsg.Message;
}
return "Measures have been added!";

7. Processing the Cube

After creating the Dimension, Measure and Cube objects, you need to process the Cube for aggregation and add the data to the Cube. The code below handles this process and can be found in the CubeAnalysisLibrary project:

//ProcessCube Library Function
DSO.MDStore objDatabase;
DSO.Cube objCube;
bool blValue = objDSOServer.MDStores.Find(strDatabase);
objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);
objCube = (DSO.Cube)objDatabase.MDStores.Item(strCubeName); 
try
{
  if(Storagemodel1) 
    objCube.OlapMode= DSO.OlapStorageModes.olapmodeRolap;
  else if (Storagemodel==2 )
    objCube.OlapMode= DSO.OlapStorageModes.olapmodeMolapIndex;
  else
    objCube.OlapMode= DSO.OlapStorageModes.olapmodeHybridIndex;
  objCube.Process(DSO.ProcessTypes.processFull);

}

catch(Exception exM)

{
  return exM.Message;

}

return "Cube process is completed";

As described previously, first I determine whether the given Database, DataSource and Cube exist in the analysis server and also check the Dimension count. I then assign the type of OLAP storage mode to the Cube object and call the processFull() method of the Cube object to calculate the aggregation of selected data.

8. Creating a Virtual Cube

Sometimes the user requirements have very large data sets – sales, purchase and other business data. So this cube has to be divided into a number of sub cubes. In these scenarios you can split the cube into a number of virtual cubes and assign each virtual cube to access only a particular role. A virtual cube encapsulates a subset of measures, dimensions and levels of one or more of existing cubes. The code below shows how to create a virtual cube:

bool blValue = objDSOServer.MDStores.Find(strDatabase); 

DSO.MDStore objDatabase, objVCube;

if(blValue)

{   
  objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);
  objVCube = (DSO.MDStore)objDatabase.MDStores.AddNew(
    strVCubeName,DSO.SubClassTypes.sbclsVirtual);
  DSO.ProcessTypes objprocesstype = new DSO.ProcessTypes();
  foreach(string strDimension in strDimensions)
  {
    if (!objVCube.Dimensions.Find(strDimension))
    {
      objVCube.Dimensions.AddNew(
        strDimension,DSO.SubClassTypes.sbclsRegular);
    }
    foreach(string strMeasure in strMeasures)
    {
      DSO.Measure objMeasur = (DSO.Measure)
      objVCube.Measures.AddNew(strMeasure,DSO.SubClassTypes.sbclsRegular);
      objMeasur.SourceColumn = "["+ strCube "].[" strMeasure +"]";
    }
    objVCube.Update();
    objVCube.Process(DSO.ProcessTypes.processFull);
    return "Virtual Cube " + strVCubeName + " Created and Processed";
  }

else
  return "No Datasource found";

First I get the Database object from the MDStores collection of server objects. I then create the virtual Cube object by calling the MDStores.AddNew() method of the Database object and passing the Cube name and SubClassType as parameters. Next I add the Dimension and Measures collection to the newly created Cube object. Then I call the Update() method of the virtual Cube to update the data in the analysis server. Finally I call the Process() method of Cube object to assign the data to the virtual Cube. In the following section we will see how to find the created Cube within the analysis server and how to the analysis data.

9. Viewing Data

At this point I have successfully created and processed a cube and a virtual cube. Now I can go to the analysis server and can see the aggregated data created in the cube. Go to Start → All Programs → Microsoft SQL Server → Analysis Server and select Analysis Services. Once the analysis manager opens, expand Analysis Services, expand the analysis server, expand Database and expand the Cubes nodes to find the newly created cube and virtual cube. Now select the newly created cube or virtual cube and in the right pane select the Data tab to see the data.

Figure 6. Data view of the cube in SQL Analysis Server

Extracting Analysis Metadata

In addition to the code that has been described in detail above there are many helper functions that are used within the user interface to populate the controls and allow the user to easily make selections. The sections below provide a code overview of these functions.

Listing Analysis Databases

Previously I discussed the creation of the DSO server object in the section Connecting to the Analysis Server. The MDStores property of DSO server object returns a list of available Databases. You can collect all these Databases into a string collection and return the collection to the user interface page.

//List Analysis Database

StringCollection strCollection = new StringCollection();

foreach(DSO.MDStore objDatabase in objDSOServer.MDStores)

{
  strCollection.Add(objDatabase.Name);

}

return strCollection;

Listing DataSources

The code below extracts the DataSources from the analysis server. It starts by getting a Database object by calling the MDStores.Item() function of the DSO object. The DataSources property of the Database object provides the collection of DataSources. I collect each item of the DataSources into a string collection and return it to the user interface page.

//List Analysis Datasources

DSO.MDStore objDatabase;

StringCollection strCollection = new StringCollection();

objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strAnalysisDatabase);

foreach(DSO.DataSource objDatasource in objDatabase.DataSources)

{
  strCollection.Add(objDatasource.Name);

}

return strCollection;

Listing Dimensions

The code below extracts the Dimensions from the analysis server. I start by calling the MDStores.Item() function of the DSO object and passing the Database name. The Dimensions property of the Database object returns a collection of Dimension objects. Now you can collect all the shared Dimensions from the Database object to the string collection and return it to the user interface page.

//List Dimensions

DSO.MDStore objDatabase;

bool blValue = objDSOServer.MDStores.Find(strDatabase);  

string strDim ="";

objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);

foreach(DSO.Dimension objDim in objDatabase.Dimensions)

{
  try
  {
    string strDSName = objDim.DataSource.Name;
    string strConnectionString=objDim.DataSource.ConnectionString;
    strDim = objDim.Name;
    strCollection.Add(strDim);
  }
  catch(Exception ex )
  {
    return ex.Message;
  }

}

return "All Dimensions returned";

Listing Tables for a Selected DataSource

The following code extracts the table names from the selected DataSource. It retrieves the connection string from the DataSource object and then connects to the data source using the connection string. It can then retrieve all the table names and return them to the user. The sample uses a MS Access database so I am using an OleDB connection string.

//Get Tables form datasource

StringCollection strCollection = new StringCollection();

string strSql= string.Empty;

DSO.MDStore objDatabase;

DSO.DataSource objDatasource;

objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);

DSO.SubClassTypes objClassTypes = new SubClassTypes();

objDatasource = (DSO.DataSource)objDatabase.DataSources.Item(strDatasource);

string strConnectionString = objDatasource.ConnectionString;

OleDbConnection objOleConn = new OleDbConnection(strConnectionString);

if(objOleConn.State == ConnectionState.Closed)
  objOleConn.Open(); 

DataTable objTable = 
  objOleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] 
  {null, null, null, "TABLE"}); 

objOleConn.Close(); 

for(int intRow = 0; intRow<objTable.Rows.Count; intRow++)

{
  strCollection.Add(objTable.Rows[intRow]2.ToString());

}

return strCollection; 

Listing Columns

The code for listing the columns also follows the same pattern as discussed previously to get the connection string. A SQL query is then used to extract the column names from the Access database. I start by creating an OLEDB data adapter by using the SQL string and connection object. I use the Fill() method of the data adapter to execute SQL query and fill the query results to the data table object. Here I have specified the condition to get the columns required for Measure objects. Measures are required to be only non string data types, because you can only aggregate using only non string data types.

//List columns

DSO.MDStore objDatabase;

DSO.DataSource objDatasource;

objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);

DSO.SubClassTypes objClassTypes = new SubClassTypes();

objDatasource = (DSO.DataSource)objDatabase.DataSources.Item(strDatasource);

string strConnectionString = objDatasource.ConnectionString;

OleDbConnection objOleConn = new OleDbConnection(strConnectionString);

objOleConn.Open(); 

StringCollection schemaCollection=new StringCollection();

string strSQL = "select * from [" + tableName.Trim() + "] Where 1<>1";

OleDbDataAdapter DA = new OleDbDataAdapter(strSQL,objOleConn);

DataTable rowset = new DataTable();

DA.Fill(rowset);

objOleConn.Close(); 

foreach (DataColumn dc in rowset.Columns )

{
  if(forMeasure)
  {
    if((dc.DataType.ToString() != "System.String") && 
      (dc.DataType.ToString() != "System.Text") && (dc.DataType.ToString()
      != "System.Char"))
    {
      schemaCollection.Add(dc.Caption);
    }
  }
  else
  schemaCollection.Add(dc.Caption);

}

return schemaCollection;

Listing Levels

The code below is used to extract the Levels of a selected Dimension. It starts by creating a Database object and passing the Database name to the MDStores.Item() function of the DSO object as a parameter. Similarly I get the Dimension object by calling the Dimensions.Item() function of the Database object and passing the Dimension name as a parameter. I then collect the Levels from the Levels property of the Dimension object to a string collection and return the string collection to the user interface.

//List Dimensions

DSO.MDStore objDatabase =  
  (DSO.MDStore)objDSOServer.MDStores.Item(strDatabase);

DSO.Dimension	objDimention =(DSO.Dimension)    
  objDatabase.Dimensions.Item(strDimName);

StringCollection strCollection = new StringCollection();

foreach(DSO.Level  objLevel in objDimention.Levels)

{
  strCollection.Add(objLevel.Name);

}

return strCollection;

Listing Cubes

The following code extracts the Cube names from the server. It starts by creating a Database object by passing the Database name to the MDStores.Item() function of the DSO object. The MDStores of the Database object is the Cube collection. Lastly it collects the Cube names into a string collection from the Database object and return it to the user interface.

//List Analysis Cube

DSO.MDStore objDatabase;

StringCollection strCollection = new StringCollection();

objDatabase = (DSO.MDStore)objDSOServer.MDStores.Item(strAnalysisDatabase);

foreach(DSO.Cube objCube in objDatabase.MDStores)

{
  try
  {
    if (objCube.IsValid)
    {
      DateTime lp = objCube.LastProcessed;
      strCollection.Add(objCube.Name);
    }
  }
  catch(Exception ex)
  {
    strCollection.Add(ex.Message);
  }

}

return strCollection;

Conclusion

This article started by providing an overview of some of the basic terminology that is used when talking about analysis services applications. This was followed by going over the basic DSO objects that were used during the creation of the sample application. La