What is MOLAP and its advantage?
MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.
Advantages:
Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
The data is compressed so it takes up less space.
And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
Cube browsing is fastest using MOLAP.
What is ROLAP and its advantage?
ROLAP (Relational Online Analytical Processing) : ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.
Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.
Advantages:
Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
Low latency.
What is HOLAP and its advantage?
Hybrid Online Analytical Processing (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. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.
Advantages:
HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
Cubes are smaller than MOLAP since the detail data is kept in the relational database.
Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
Low latency since processing takes place when changes occur and detail data is kept in the relational database.
What are Translations and its use?
Translation: The translation feature in analysis service allows you to display caption and attributes names that correspond to a specific language. It helps in providing GLOBALIZATION to the Cube.
What is Database dimension?
All the dimensions that are created using NEW DIMENSION Wizard are database dimensions. In other words, the dimensions which are at Database level are called Database Dimensions.
What is Cube dimension?
A cube dimension is an instance of a database dimension within a cube is called as cube dimension. A database dimension can be used in multiple cubes, and multiple cube dimensions can be based on a single database dimension
Difference between Database dimension and Cube dimension?
The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.
Database dimension is created one where as Cube dimension is referenced from database dimension.
Database dimension exists only once.where as Cube dimensions can be created more than one using ROLE PLAYING Dimensions concept.
How will you add a dimension to cube?
To add a dimension to a cube follow these steps.
In Solution Explorer, right-click the cube, and then click View Designer.
In the Design tab for the cube, click the Dimension Usage tab.
Either click the Add Cube Dimension button, or right-click anywhere on the work surface and then click Add Cube Dimension.
In the Add Cube Dimension dialog box, use one of the following steps:
To add an existing dimension, select the dimension, and then click OK.
To create a new dimension to add to the cube, click New dimension, and then follow the steps in the Dimension Wizard.
What is SCD (slowly changing dimension)?
Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. Implementing the SCD mechanism enables users to know to which category an item belonged to in any given date.
What are types of SCD?
It is a concept of STORING Historical Changes and when ever an IT guy finds a new way to store then a new Type will come into picture. Basically there are 3 types of SCD they are given below
SCD type1
SCD type2
SCD type3
What is role playing dimension with two examples?
Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A dimension can play different role in a fact table you can recognize a roleplay dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table.
Ex1: There are three dimension keys in the factinternalsales,factresellersales tables which all refer to the dimtime table,the same time dimension is used to track sales by that contain either of these fact table,the corresponding role-playing dimension are automatically added to the cube.
Ex2 : In retail banking, for checking account cube we could have transaction date dimension and effective date dimension. Both dimensions have date, month, quarter and year attributes. The formats of attributes are the same on both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format. Both dimensions have members from 1993 to 2010.
What is measure group, measure?
Measure groups : These measure groups can contain different dimensions and be at different granularity but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.
Measures : Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.
What is attribute?
An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.
What is surrogate key?
A surrogate key is the SQL generated key which acts like an alternate primary key for the table in database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. 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.
Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.
How many types of relations are there between dimension and measure group?
They are six relation between the dimension and measure group, they are
No Relationship
Regular
Refernce
Many to Many
Data Mining
Fact
How you move the cube from one server to another?
There are many ways to do the same. Let me explain four here and cleverly you can say “I worked on 4 SSAS projects till date and implemented different types in all the four.”
Backup and restore – This is the simplest way. Take the Backup from development server and copy the backup to FTP folder of clients. After doing this drop a mail to Client’s Admin and he will take care of RESTORE part.
Directly PROCESS the cube in PRODUCTION environment. For this you need access to Production which will not be given by clients unless the clients are *********. One of the client I worked for given FULL access to me ..
Under Srart –> All Programs –> Sql Server –> Analysis Services you can see deployment wizard. This is one way of moving the cube. This method has some steps to follow. First deploy your cube and FOUR files will be created in BIN folder of PROJECT folder. Copy those FOUR files and paste in Production server in any directory. Then OPEN this DEPLOYMENT Wizard in production and when it ask for Database file then point to the location where you copied the files. After that NEXT,NEXT,NEXT … OK .. Cube will be deployed and processed.
This way is most beautiful one. Synchronization, In this we will first deploy and process the cube in STAGING ENVIRONMENT and then we will go to production server. Connect to Analysis services in SSMS and select Synchronize by right clicking on Databases folder in SSMS of analysis services. Then select source as STAGING SERVER and then click on OK. The changes in the cube present in the Staging server will be copied to the production server.
What is the toughest challenge you face in your Project?
There are couple of this where we face difficulty.
While working on RELATIONSHIPS between Measure Groups and Dimensions.
Working on Complex calculations
Performance tuning
How you created Partitions of the cube in your Last Project?
Partitions can be created on different data. Few people do it on PRODUCT NAME wise and many prefer to do it on DATE data wise. you go with DATE wise.
In dates, we can create MONTH wise,WEEK wise,QUARTER wise and some times YEAR wise. This all depends on how much data you are coming per WEEK or MONTH or QUARTER … If you are getting 50 lakhs records per month then tell you do MONTH wise.
How many dimensions in your last cube?
47 to 50.
How many measure groups in your last cube?
Total 10 and in that 4 are Fact tables and remaining 6 are Fact less fact tables.
What is the Schema of your last cube?
Snowflake
Why not STAR Schema ?
My data base design doesn’t support STAR Schema.
What are the different relationships that you are used in your cube?
Regular
Referenced
Many to Many
Fact
No Relationship
Have you created the KPI’s , If then Explain?
Don’t add much to this as the questions in this will be tricky. Just tell that you worked on couple of KPI and you have basic knowledge on this. (Don’t worry, this is not MANDATORY)
How you define Aggregations in your Project?
We defined the aggregations for MOST FREQUENTLY USED data in SSRS reports.
Size of SSAS team in your last Project?
Just 2 guys as we guys are really in demand and lot of scarcity:)
How many Resources worked on same Cube in your Project?
Only 2 and one in morning shift and another in Evening shift.
What are Aggregations and its use?
Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard. This wizard guides you through the following steps:
Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.
MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.
Advantages:
Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
The data is compressed so it takes up less space.
And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
Cube browsing is fastest using MOLAP.
What is ROLAP and its advantage?
ROLAP (Relational Online Analytical Processing) : ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.
Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.
Advantages:
Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
Low latency.
What is HOLAP and its advantage?
Hybrid Online Analytical Processing (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. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.
Advantages:
HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
Cubes are smaller than MOLAP since the detail data is kept in the relational database.
Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
Low latency since processing takes place when changes occur and detail data is kept in the relational database.
What are Translations and its use?
Translation: The translation feature in analysis service allows you to display caption and attributes names that correspond to a specific language. It helps in providing GLOBALIZATION to the Cube.
 |
| Spotify Technology Frequently Asked SSAS Interview Questions Answers |
What is Database dimension?
All the dimensions that are created using NEW DIMENSION Wizard are database dimensions. In other words, the dimensions which are at Database level are called Database Dimensions.
What is Cube dimension?
A cube dimension is an instance of a database dimension within a cube is called as cube dimension. A database dimension can be used in multiple cubes, and multiple cube dimensions can be based on a single database dimension
Difference between Database dimension and Cube dimension?
The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.
Database dimension is created one where as Cube dimension is referenced from database dimension.
Database dimension exists only once.where as Cube dimensions can be created more than one using ROLE PLAYING Dimensions concept.
How will you add a dimension to cube?
To add a dimension to a cube follow these steps.
In Solution Explorer, right-click the cube, and then click View Designer.
In the Design tab for the cube, click the Dimension Usage tab.
Either click the Add Cube Dimension button, or right-click anywhere on the work surface and then click Add Cube Dimension.
In the Add Cube Dimension dialog box, use one of the following steps:
To add an existing dimension, select the dimension, and then click OK.
To create a new dimension to add to the cube, click New dimension, and then follow the steps in the Dimension Wizard.
What is SCD (slowly changing dimension)?
Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. Implementing the SCD mechanism enables users to know to which category an item belonged to in any given date.
What are types of SCD?
It is a concept of STORING Historical Changes and when ever an IT guy finds a new way to store then a new Type will come into picture. Basically there are 3 types of SCD they are given below
SCD type1
SCD type2
SCD type3
What is role playing dimension with two examples?
Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A dimension can play different role in a fact table you can recognize a roleplay dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table.
Ex1: There are three dimension keys in the factinternalsales,factresellersales tables which all refer to the dimtime table,the same time dimension is used to track sales by that contain either of these fact table,the corresponding role-playing dimension are automatically added to the cube.
Ex2 : In retail banking, for checking account cube we could have transaction date dimension and effective date dimension. Both dimensions have date, month, quarter and year attributes. The formats of attributes are the same on both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format. Both dimensions have members from 1993 to 2010.
What is measure group, measure?
Measure groups : These measure groups can contain different dimensions and be at different granularity but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.
Measures : Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.
What is attribute?
An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.
What is surrogate key?
A surrogate key is the SQL generated key which acts like an alternate primary key for the table in database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. 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.
Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.
How many types of relations are there between dimension and measure group?
They are six relation between the dimension and measure group, they are
No Relationship
Regular
Refernce
Many to Many
Data Mining
Fact
How you move the cube from one server to another?
There are many ways to do the same. Let me explain four here and cleverly you can say “I worked on 4 SSAS projects till date and implemented different types in all the four.”
Backup and restore – This is the simplest way. Take the Backup from development server and copy the backup to FTP folder of clients. After doing this drop a mail to Client’s Admin and he will take care of RESTORE part.
Directly PROCESS the cube in PRODUCTION environment. For this you need access to Production which will not be given by clients unless the clients are *********. One of the client I worked for given FULL access to me ..
Under Srart –> All Programs –> Sql Server –> Analysis Services you can see deployment wizard. This is one way of moving the cube. This method has some steps to follow. First deploy your cube and FOUR files will be created in BIN folder of PROJECT folder. Copy those FOUR files and paste in Production server in any directory. Then OPEN this DEPLOYMENT Wizard in production and when it ask for Database file then point to the location where you copied the files. After that NEXT,NEXT,NEXT … OK .. Cube will be deployed and processed.
This way is most beautiful one. Synchronization, In this we will first deploy and process the cube in STAGING ENVIRONMENT and then we will go to production server. Connect to Analysis services in SSMS and select Synchronize by right clicking on Databases folder in SSMS of analysis services. Then select source as STAGING SERVER and then click on OK. The changes in the cube present in the Staging server will be copied to the production server.
What is the toughest challenge you face in your Project?
There are couple of this where we face difficulty.
While working on RELATIONSHIPS between Measure Groups and Dimensions.
Working on Complex calculations
Performance tuning
How you created Partitions of the cube in your Last Project?
Partitions can be created on different data. Few people do it on PRODUCT NAME wise and many prefer to do it on DATE data wise. you go with DATE wise.
In dates, we can create MONTH wise,WEEK wise,QUARTER wise and some times YEAR wise. This all depends on how much data you are coming per WEEK or MONTH or QUARTER … If you are getting 50 lakhs records per month then tell you do MONTH wise.
How many dimensions in your last cube?
47 to 50.
How many measure groups in your last cube?
Total 10 and in that 4 are Fact tables and remaining 6 are Fact less fact tables.
What is the Schema of your last cube?
Snowflake
Why not STAR Schema ?
My data base design doesn’t support STAR Schema.
What are the different relationships that you are used in your cube?
Regular
Referenced
Many to Many
Fact
No Relationship
Have you created the KPI’s , If then Explain?
Don’t add much to this as the questions in this will be tricky. Just tell that you worked on couple of KPI and you have basic knowledge on this. (Don’t worry, this is not MANDATORY)
How you define Aggregations in your Project?
We defined the aggregations for MOST FREQUENTLY USED data in SSRS reports.
Size of SSAS team in your last Project?
Just 2 guys as we guys are really in demand and lot of scarcity:)
How many Resources worked on same Cube in your Project?
Only 2 and one in morning shift and another in Evening shift.
What are Aggregations and its use?
Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard. This wizard guides you through the following steps:
Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.