XOLAP - Virtual cubes against a Data Warehouse Part 1

Can it be true? Real time ad hoc analysis against a Data Warehouse using an Essbase cube that contains no data?

Well with XOLAP, these capabilities a being brought together. I have created a brief tutorial within this article to demonstrate to overall concept relating to XOLAP.

In Part 1 of this article, I'll discuss:

• Setup that needs to occur to emulate sample
  
• Background into XOLAP
  
• Current restrictions relating to XOLAP
  
• Creating Data Sources in Essbase Studio
  
• Defining a MiniSchema
  
• Defining Standard Hierarchies

Due to the number of screen shots and the size of this blog article, I have set the image properties to small. While the screens may be difficult to decipher within the article, each can be clicked on to be rendered in a much larger resolution for viewing.

Setup The Needs to Occur to Emulate Sample

The example delivered in this article involves leveraging AdventureWorksBI.msi on SQL Server 2005 as the Data Warehouse. This database can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/4004 . The installer for this download requires you to manually attach the database after installation.

Within the dbo.DimCustomer table add a new column called "ProperName" with a property of "nvarchar(100)."

Update the ProperName column within dbo.DimCustomer using a SQL statement similar to the following :

Update dbo.DimCustomer
Set ProperName = LastName + ', ' + FirstName + ' ' + MiddleName

Within the dbo.DimTime table add 3 new columns called "Month", "Day" and "Year" with each having a property of "nchar(10)."

Update the newly added columns within dbo.DimCustomer using a SQL statement similar to the following :

Update dbo.DimTime
Set Month = DatePart(Month,FullDateAlternateKey)
Set Day = DatePart(Day,FullDateAlternateKey)
Set Year = DatePart(Year,FullDateAlternateKey)

A little background into XOLAP

XOLAP (extended online analytic processing) is a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates a source relational database with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database.

OLAP and XOLAP store the metadata outline and the underlying data in different locations:

In OLAP, the metadata and the underlying data are located in the Essbase database.

In XOLAP, the metadata is located in the Essbase database and the underlying data remains in your source relational database.

Restrictions For XOLAP

• No editing of an XOLAP cube is allowed. To modify an outline, you must create a new outline in Essbase Studio. XOLAP operations will not automatically incorporate changes in the structures and the contents of the dimension tables after an outline is created.
  
• When derived text measures are used in cube schemas to build an Essbase model, XOLAP is not available for the model.
  
• XOLAP can be used only with aggregate storage. The database is automatically duplicate-member enabled.
  
• Alternate hierarchies and attribute dimensions are supported; however, attribute hierarchies are not supported.
  
• XOLAP supports dimensions that do not have a corresponding schema-mapping in the catalog; however, in such dimensions, only one member can be a stored member.
  
• A model that is designated as XOLAP-enabled must be deployed to a new Essbase database because incremental builds for XOLAP are not supported.
  

Creating a Data Source

• From the "Essbase Studio - Getting Started" page within Essbase Studio, select the hot link "Data Source Wizard", the "Define Connection" portion of the Connection Wizard is displayed.

• Enter a Connection Name.
  
• Enter an optional Description.
  
• Select the appropriate Data Source Type. For example, if you are creating a connection to a Microsoft SQL Server data source, select Microsoft SQL Server from the drop-down list.
  
• In Server Name, enter the name of server where the database resides.
  
• To use a port number other than the default, clear the Default check box next to Port and enter the correct port number in the text box.If you are using the default port number, you can skip this step.
  
• Enter the User Name and Password for this database.
  
• In Database Name, select "AdventureWorksDB"
  
• Click Test Connection. If the information you entered in the wizard is correct, a message confirms a successful connection.If you entered incorrect information in the wizard, a message is displayed explaining that invalid credentials have been provided. Correct the errors and retest until the connection is successful.
  
• Clicking Next takes you to the Select Tables page of the wizard

Select the following tables for the Select Tables dialog box and then select "Next":

• dbo.DimCurrency
• dbo.DimCustomer
• dbo.DimProduct
• dbo.DimProductSubCategory
• dbo.DimPromotion
• dbo.DimSalesTerritory
• dbo.DimTime
• dbo.FactInternetSales
  
  

The Select MiniSchema dialog is now presented.

• Select the radial button for "Create a new schema diagram"
• Enter a name for this schema, In this instance I used "XOLAP Adventure Works DWSchema"
• Leave "Skip Schema" and "Use Introspection to Detect Hierarchies" unchecked
• Select Next
  
  
• The "Populate Schema" dilog box is displayed. Each of the tables that were previosly selected should be displayed on the right hand panel.
• Select "Next"
  
  
• The "Create Metadata Element" dialog box should now be displayed.
• Toggle on the radial button next to the "XOLAP Adventure Works DW", this should toggle on all members displayed in this dialog window
• Select "Next"
  
  
• The "XOLAP Adventure Works DWSchema" should now be displayed.
  
  
• Navigate back to the "Welcome" screen
• Highlight the "DimSalesTerritory" within the Metadata Navigator. Once highlighted, select the "Hierarchies" hotlink from the "Welcome" screen.
  
  
• Above is a representation of the contents from the DimSalesTerritory table. This is provided to deliver an understanding of how the hierarchy will be developed from the underlying relational table
  
  
• The hierarchy wizard should now be displayed at this point.
• Specify the "Dimension Head Name", in this case "Total Sales Territory" and then drag the "SalesTerritoryGroup" data element from the Metadata Navigator into the Data grid.
• The grid should should now contain the "SalesTerritoryGroup" element. Highlight this element and click "Add" and select "Child"
  
  
• Within the "Select Entity" dialog box, select "SalesTerritoryCountry" and then "OK"
  
  
• Leveraging the same approach for you used for adding "SalesTerritoryCountry", now add "SalesTerritoryRegion" to deliver a hierarchy as depicted above.
  
  
• By right clicking on the "Total Sales Territory" hierarchy data element in the MetaData Navigator panel and selecting "Preview Hierarchy" the above sampling of data should be displayed
  
  
• 
  
  Next, we will create the Time hierarchy for the model. This step will be slightly different, we will leverage the newly create columns in SQL server to build out a time hierarchy.
• With the DimTime metadata element highlighted in the MetaData Navigator, right click and select "New" and "Dimension Element"
  
  
  
  
• The "Edit Properties" dialog window for the new data element should appear.
• Enter "Month Day Year" in the name window
• Paste the following syntax into the Caption Binding window:
• 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Month' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Day' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )
  
  
• Repeat the following steps for "Month Year" and place the following syntax in the caption binding window for "Month Year":
• trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Month' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )
  
  
• Repeat the following steps for "Year" and place the following syntax in the caption binding window for "Year":
• 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )
  

In my next blog, which should be published shortly I'll address the following:

• Completing the Time Hierarchy
• Developing the rest of the standard dimensions
• Developing a measures dimension
• Creating the cube schema
• Deploying the cube
• Querying the data
• Showing real time data updates