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 ?
- confirmed dimension — The value will be refered by more than one dimension table
- junk dimension — Combined values of transaction
- degenerate attribute — If a dimension data kept on a fact table for reference
- 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
If the attributes are clearly related
EG : years->quarter->month->week
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
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