SSAS Q/A

SSAS

What are the Types of Schema Structure in SSAS?
1. Star Schema
2. SnowFlake Schema
3. StarFlake Schema : A hybrid structure that contains a mixture of star(denormalized) and snowflake(normalized) schema.

What are types of storage modes?
There are three standard storage modes in OLAP they are
1.MOLAP  — the data and aggregations are stored in a multidimensional format
2.ROLAP  — data and aggregations are kept in the relational database instead of on the OLAP server
3.HOLAP  — HOLAP is a combination of MOLAP and ROLAP HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format

What is a DataMart?
A data mart is a subset of an organizational data store. Datamart focus on specific business functions for a specific organization.

What is a DataWarehouse ?
A collection of DataMart forms a Datawarehouse.

What is a Fact Table ?
The table which contains METRICS. This table will store only the measure values.

What is a Factless Fact Table ?
Fact table will not have a measure they will have only the key values refered to the dimension are called factless fact table.

Below are the most common types of measures/facts:

Fully Additive Facts: This measure which can be added across all the associated dimensions.
Example: sales amount is a fact which can be summed across different dimensions like customer, geography, date, etc.

Semi-Additive Facts: These are facts which can be added across only few dimensions.
Example: bank balance is a fact which can be summed across the customer dimension but it cannot be added across the date dimension

Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube.
Example: profit margin is a fact which cannot be added across any of the dimensions. we can not add against product dimension.

Derived Facts: Derived facts are the facts which are calculated from one or more facts,
For example: profit margin.Which is calculated when needed

Factless Facts: Fact table will not have a measure they will have only the key values(Foreign key) refered to the dimension are called factless fact table.

What are the Types of Dimension Tables ?

  1. confirmed dimension    — The value will be refered by more than one dimension table
  2. junk dimension         — Combined values of transaction
  3. degenerate attribute   — If a dimension data kept on a fact table for reference
  4. Role Playing Dimension — Date dimension which act as a orderdate dimension and purchasedate dimension.

What is a Hierarchy ?
hierarchies  is made up of multiple levels created based on the requirement

->years->quarter->month->week These are all the levels of calender hierarchy

They are 2 types of hierarchies they are
1.Natural hierarchy
If the attributes are clearly related
EG : years->quarter->month->week

2.Unnatural hierarchy
If the attributes are not clearly related
country -> state -> city (Not clear where to give the county and province details)

How to Create a Cube?
1.Create  a data source
2.Create a datasource view
3.Create Dimensions
4.Create Fact table
4.Create a cube
5.Deploy and Process the cube

What is surrogate key?
A primary difference between a primary key and surrogate key in few databases is that primarykey uniquely identifies a record while a Surrogatekey uniquely identifies an entity of record.
Use identity to create the Surrogate key.

What are KPIs?
KPI are normally represents with some graphical items such as traffic signals,ganze

What are types of SCD?
1.SCD type1   — Will not accept change
2.SCD type2   — Will store the history information
3.SCD type3   — Add column to store the history information

What is a partition?
Queries run faster against a partitioned cube because Analysis Services only needs to read data from the partitions that contain the answers to the queries. Partitions are a powerful and flexible means of managing cubes, especially large cubes

SSAS Q/A was last modified: October 1st, 2017 by Justin Antony

Leave a Reply

Your email address will not be published.