What is a SSAS data cube?
SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions.
In simple terms, you can use SSAS to create cubes using data from data warehouse for deeper and faster data analysis.
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 measurable details
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.
Time can also have its own hierarchy like Year->Quarter->Month.
SSAS Terminology
Dimension Table
Dimensions provide the context surrounding a business process event. In simple terms, they give who, what, where of a fact. In the Sales business process, for the fact quarterly sales number, dimensions would be
- Who – Customer Names
- Where – Location
- What – Product Name
In other words, a dimension is a window to view information in the facts.
SSAS table |
BPW Table |
M3 table |
Item |
Dim_Items |
MITMAS |
Customer |
Dim_Customers |
OCUSMA |
Customer Delivery Address |
Dim_Customers_DeliveryAddresses |
OCUSAD |
Fact Table
A fact table is the most important table in a dimensional model. A Fact Table contains measurements/fact and foreign keys to the dimension tables. A quick example of a fact table is the Sales table.
SSAS table |
BPW Table |
M3 table |
Sales |
K_Customer_SalesAnalysis |
OSBSTD |
Stock Evolution Data |
FT_InventoryTracking |
MITLOC |
Measure
A measure represents a column that contains quantifiable data, usually numeric, that can be aggregated.
Examples of measures: [Kg’s], [Actual vs Budget Net Sales], [Gross Margin YoY]