SQL Server Analysis Services
Designing OLAP Data Cubes OLAP Data cubes represent the primary logical object for providing critical, multidimensional analysis common to business intelligence which may be viewed using a variety of tools such as BIDS or Excel. Cubes contain data from two types of tables – a fact or “measure” table (typically one) and multiple dimension tables.…
The Data Store for OLAP Analysis Before an organization may engage in OLAP analysis, leveraging cubes, the data must be denormalized from its primary operational OLTP system into a data warehouse, which consists of measures or “fact tables” for analysis, supporting by dimensions which provide context to the organization’s facts. Once analytical requirements have been…
The Foundation of Business Intelligence (BI) Online Analytical Processing (OLAP) is the mechanism by which vast amounts of data is leveraged for strategic decision-making, allowing organizations desiring to achieve and maintain a competitive lead in their market. OLAP is chosen for its ability to deliver faster querying capability, from numerous types of data stores, utilizing…
Please see my other Business Intelligence articles. SQL Server Analysis Services (SSAS) An Overview of OLAP BIDS Cube Browser BIDS Exploring a Cube Creating a Cube Creating a Named Calculation Data Cube Data Warehouse Designing a Cube Dimension Hierarchies OLAP Schemas Refining a Dimension Viewing a Cube in Excel
Business Intelligence (BI) encompasses leveraging descriptive and inferential statistical analysis upon massive organizational data stores to enable strategic decision-making. SQL Server Integration Services (SSIS) SQL Server Integration Services (SSIS) is the most-used tool for Extract-Transform-Load (ETL) operations inherent within BI solutions. SQL Server Reporting Services (SSRS) SQL Server Reporting Services (SSRS) represents the most popular reporting platform,…
Adding New Dimension Fields Often in the cycle of cube development it become necessary to add new fields to a dimension for future reporting purposes. In this article, I will demonstrate adding an aggregation of [City, State] to the data source view. First, I’ll open the AW data source view. Now I need to find…
Adding New Dimension Fields Often in the cycle of cube development it become necessary to add new fields to a dimension for future reporting purposes. In this article, I will demonstrate adding an aggregation of [City, State] to the data source view. First, I’ll open the AW data source view. Now I need to find…
Refining Cube Dimensions Once a cube has been created, dimensions should be refined by updating attributes and hierarchies. With respect to hierarchies, there are two primary types – natural and reporting. Natural Natural hierarchies contains relationships between levels and expand as they are traversed. One example of a natural hierarchy resides within the Geography dimension.…
Implementing a Cube Design Now that I’ve covered the basics of how to design a cube, I will demonstrate the basics of creating a cube. Within Solution Explorer, launch the New Cube Wizard. Proceed to the second step and select to Use existing tables and click Next. Select a measure group(s) which contain measures and dimensions…
Using BIDS to Explorer Cube Characteristics and Data With my cube designed and created, I will now leverage the BIDS interface to expose its rich features. First, I’ll select the Customer dimension to Explore Data. Now I’m presented with a series of sub-tabs which I may use to view Table data, use a Pivot Table, view…
TechMbaBi 