|
Data Warehouse |
|
|
[SSAS] |
|
|
Questions: |
Notes: |
|
What is Data Explosion? |
Data Explosions are when too many aggregations are made on hierarchies that aren’t used.
These aggregations increase exponentially until they become too big and slow down your cube. |
|
What is Proactive Caching? |
The data that Analysis Services needs to respond to a query can exist in one of three storage locations, also called caches.
These caches store data in memory which allows for quicker retrieval of data.
With these caches, you can refresh the data in memory on a periodic basis or when data in the db source changes via notifications from SQL server. |
|
What are Hierarchies in SSAS? |
|
|
What is the difference between Natural and Unnatural Hierarchies? |
|
|
What is the difference between balanced and unbalanced hierarchies? |
|
|
What is a ragged hierarchy? |
|
|
What is Dimension Usage |
|
|
What are KPI? |
|
The process of creating OLAP cubes from an existing structures is BOTTOM UP approach. It’s the most common way of creating an OLAP Cube.
We can also create a cube by Top Down approach where we design the cube structure first and then we populate the cube structure after the data warehouse design.
(Not commonly used).
You can use top down approach to add few new tables to the existing system or make changes to the existing system if they don’t fit the user requirement.
There are two main scenarios in which you might need to add a new measure group to an existing system:
* When a new fact table has been added to the database and you need to bring its data into the cube. Because a measure group typically is bound to a single fact table, you would need as many measure group as there are fact tables in the data warehouse.
* When you need a Distinct Count measure that calculates the number of fact rows with unique values, such as the number of unique customers who have placed orders.
NB. Because of the required server overhead for storing Distinct Count measures, a measure group can have only one measure with a Distinct Count aggregation function.
Measures:
Additive Measures: They can be added over any dimension (including time)
eg. Sales amount for each day or anything over time.
Semi Additive- They can be added over any dimension except time.
Eg:Bank balances, stock Inventory(First non empty child, Last Non empty child)
Non Additive-they cannot be added over any dimension.
Eg. Percentages, Rates(Average of children)
Design Hierarchies and Attribute Relationships
Hierarchies- Creating Hierarchies is nothing but Arranging the attributes into levels based on their relationships which will improve the performance. Also to extend the dimensionality of the user for analysis.
