Cubes are multi-dimensional data sources which have dimensions and facts (also known as measures) as its basic constituents. From a relational perspective dimensions can be thought of as master tables and facts can be thought of as measureable details. These details are generally stored in a pre-aggregated proprietary format and users can analyze huge amounts of data and slice this data by dimensions very easily. Multi-dimensional expression (MDX) is the query language used to query a cube, similar to the way T-SQL is used to query a table in SQL Server.
Simple examples of dimensions can be product / geography / time / customer, and similar simple examples of facts can be orders / sales. A typical analysis could be to analyze sales in Asia-pacific geography during the past 5 years. You can think of this data as a pivot table where geography is the column-axis and years is the row axis, and sales can be seen as the values. Geography can also have its own hierarchy like Country->City->State. Time can also have its own hierarchy like Year->Semester->Quarter. Sales could then be analyzed using any of these hierarchies for effective data analysis.
A typical higher level cube development process using SSAS involves the following steps:
1) Reading data from a dimensional model
2) Configuring a schema in BIDS (Business Intelligence Development Studio)
3) Creating dimensions, measures and cubes from this schema
4) Fine tuning the cube as per the requirements
5) Deploying the cube
In this tutorial we will step through a number of topics that you need to understand in order to successfully create a basic cube. Our high level outline is as follows:
Design and develop a star-schema
Create dimensions, hierarchies, and cubes
Process and deploy a cube
Develop calculated measures and named sets using MDX
Browse the cube data using Excel as the client tool
When you start learning SSAS, you should have a reasonable relational database background. But when you start working in a multi-dimensional environment, you need to stop thinking from a two-dimensional (relational database) perspective, which will develop over time.
In this tutorial, we will also try to develop an understanding of OLAP development from the eyes of an OLTP practitioner.
Creating a Sample SSAS Project and Cube
Data in Online Transaction Processing (OLTP) systems is suited to support convenient data storage for user-facing applications. The data model in such systems is highly normalized. For data warehousing environments, data is required to be in a schema that supports a dimensional model. Data is therefore transformed from the OLTP storage systems to a data warehouse using ETL, so that data can be aligned in a suitable format to create data marts from the data warehouse.
Two major theories driving the design of a data warehouse and data marts are from Ralph Kimball and Bill Inmon which are mostly practiced in real time environments. Generally data is gathered from OLTP systems and brought to the data warehouse. From the data warehouse, context / requirement specific data marts are created, which can be perceived as a subset of the data warehouse. Cube source data from these data marts, and client applications connect to the cube. The schema for a cube falls into two categories: Star and Snowflake. In simple terms, Star Schema can be considered a more denormalized form of schema compared to Snowflake.
Designing and developing a data warehouse is out scope for this tutorial. For the purpose of development, we will install and use the AdventureWorks DW database. We will then create a SSAS project and create a data source which will connect to this database. Finally we will create a star schema using a Data Source View.
ABOVE JAI KRISHNA BAJAJ MOTORCYCLES,
OPPOSITE TO MITULA JWELLERY,