Building OLAP Cubes
Discovery Hub includes support for designing Online Analytical Processing (OLAP) cubes that end users can browse through Excel or specialized business intelligence front-ends. OLAP utilizes the fact- and dimension tables you have created in your data warehouse.
Cubes allow you to present data in a multidimensional model. You can break down data in your data warehouse into smaller units, enabling you to drill-down, or roll-up through data, depending on the level of detail you want to view. You can, for example, create a sales cube, a production cube, a finance cube, and so on.
A cube consists of a number of dimensions and measures. The dimensions determine the structure of the cube, and the measures represent the numerical values. You can, furthermore, define hierarchies within a dimension by using dimension levels.
Dimensions define how a user looks at data in a cube. Dimensions are created independently of a particular cube and can be used within several cubes at the same time. The same dimension can also be utilized several times within the same cube, which is referred to as a role-playing dimension. A common example of this would be the Date dimension, which can represent both the Document Date and Posting Date in a cube, thus having a single dimension play two roles.
To use OLAP, you will first need to add an OLAP Server.
- In the Solution Explorer, right-click Semantic Layer, click Add OLAP Server, and then click New OLAP server...
- In the Name field, type a name for the OLAP server. The name cannot exceed 15 characters in length.
- In the Server Name box, type the name of the OLAP database server.
- In the Database box, type a name for the database.
- In the Collation list, click the database collation to use for the OLAP database. <Server Default> will inherit the default collation currently set in Analysis Services. <Application Default> will use Latin1_General_CI_AS. This collation should correspond with the collation that is set for SQL Analysis Services.
- Select a data warehouse from the Data Warehouse list, and then click OK. Each OLAP database can pull from a single data warehouse database.
Note: If you choose to delete the OLAP database later, it will remove the database from the project, but it will not delete the physical database on the OLAP Server itself. This must be done manually through SQL Server Management Studio.