Are you searching for SQL Server Analysis Server job? SSAS is software tool that does the data processing and mining in SQL Server. Companies use this tool to analyze and spread information across. Many Organizations are awaiting for SQL Server Analysis Services job candidates for several roles maintain the huge amounts of data. Job opportunities are present everywhere for this technology. SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack to develop Online Analytical Processing (OLAP) solutions. SSAS job description might include experience on handling the relational database using queries. Wisdomjobs created interview questions exclusively for the candidates who are in search of job. Do check our page for SQL Server Analysis Services interview questions and answers to get set for the interview.
Question 1. How Many Types Of Dimensions Are There And What Are They?
Answer :
They are 3 types of dimensions:
Question 2. How Many Types Of Attribute Relationships Are There?
Answer :
They are 2 types of attribute relationships they are
Question 3. What Is Attribute Relationships, Why We Need It?
Answer :
Attribute relationships are the way of telling the analysis service engine that how the attributes are related with each other. It will help to relate two or more attributes to each other.Processing time will be decreased if proper relationships are given. This increases the Cube Processing performance and MDX query performance too.
In Microsoft SQL Server Analysis Services, attributes within a dimension are always related either directly or indirectly to the key attribute. When you define a dimension based on a star schema, which is where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension.
When you define a dimension based on a snowflake schema, which is where dimension attributes are derived from multiple related tables, an attribute relationship is automatically defined as follows:
Question 4. What Is Factless Fact Table?
Answer :
This is very important interview question. The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.
Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected
Question 5. What Is Fact Table?
Answer :
A fact table contains the basic information that you wish to summarize. The table that stores the detailed value for measure is called fact table. In simple and best we can define as “The table which contains METRICS” that are used to analyse the business.
It consists of 2 sections:
Question 6. What Is Dimension Table?
Answer :
A dimension table contains hierarchical data by which you’d like to summarize. A dimension table contains specific business information, a dimension table that contains the specific name of each member of the dimension. The name of the dimension member is called an “attribute”
The key attribute in the dimension must contain a unique value for each member of the dimension. This key attribute is called “primary key column”
The primary key column of each dimension table corresponding to the one of the key column in any related fact table.
Question 7. How Will You Add A New Column To An Existing Table In Data Source View?
Answer :
By using named calculations we can add a new column to an existing table in the data source view.
Question 8. Why We Need Named Queries?
Answer :
A named query is used to join multiple tables, to remove unnecessary columns from a table of a database. You can achieve the same in database using Views but this Named Queries will be the best bet whe you don’t have access to create Views in database.
Question 9. What Is Named Query?
Answer :
Named query in DSV is similar to View in Database. This is used to create Virtual table in DSV which will not impact the underlying database. Named query is mainly used to merge the two or more table in the datasource view or to filter columns of a table.
Question 10. What Is Data Source View Or Dsv?
Answer :
A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer.
Question 11. What Is Named Calculation?
Answer :
A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source.
Named calculation is used to create a new column in the DSV using hard coded values or by using existing columns or even with both.
Question 12. What Is A Data Source Or Ds?
Answer :
The data source is the Physical Connection information that analysis service uses to connect to the database that host the data. The data source contains the connection string which specifies the server and the database hosting the data as well as any necessary authentication credentials.
Question 13. Explain The Flow Of Creating A Cube?
Answer :
Steps to create a cube in ssas:
Question 14. What Are The Difference Between Data Mart And Data Warehouse?
Answer :
Datawarehouse is complete data where as Data mart is Subset of the same.
Ex: All the organisation data may related to finance department, HR, banking dept are stored in data warehouse where as in data mart only finance data or HR department data will be stored. So data warehouse is a collection of different data marts.
Question 15. What Is Data Mart?
Answer :
A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization.
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
They are 3 types of data mart they are:
Question 16. What Is Data Warehouse In Short Dwh?
Answer :
The data warehouse is an informational environment that:
Question 17. What Is The Difference Between Ssas 2005 And Ssas2008?
Answer :
Question 18. How You Provide Security To Cube?
Answer :
By defining roles we provide security to cubes. Using roles we can restrict users from accessing restricted data.
Procedure as follows:
Answer :
The full form of AMO is Analysis Management Objects. This is used to create or alter cubes from .NET code.
Answer :
Just open the datasourceview and on right click we find the option REFRESH. Click the REFRESH then it will add new attributes to the table which can be added to Cube.
Answer :
The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that’s been aggregated to allow queries to return data quickly.
For example, a cube of order data might be aggregated by time period and by title, making the cube fast when you ask questions concerning orders by week or orders by title.
Question 22. What Are The Types Of Processing And Explain Each?
Answer :
They are 6 types of processing in ssas ,they are
Question 23. What Is The Maximum Size Of A Dimension?
Answer :
The maximum size of the dimension is 4 gb.
Question 24. What Is Deploy, Process And Build?
Answer :
Bulid: Verifies the project files and create several local files.
Deploy: Deploy the structure of the cube(Skeleton) to the server.
Process: Read the data from the source and build the dimensions and cube structures
Question 25. What Is Perspective, Have You Ever Created Perspective?
Answer :
Perspectives are a way to reduce the complexity of cubes by hidden elements like measure groups, measures, dimensions, hierarchies etc. It’s nothing but slicing of a cube, for ex we are having retail and hospital data and end user is subscribed to see only hospital data, then we can create perspective according to it.
Question 26. What Are Aggregations And Its Use?
Answer :
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:
Question 27. What Is The Minimum And Maximum Number Of Partitions Required For A Measure Group?
Answer :
Question 28. What Is Partition, How Will You Implement It?
Answer :
You can use the Partition Wizard to define partitions for a measure group in a cube. By default, a single partition is defined for each measure group in a cube. Access and processing performance, however, can degrade for large partitions. By creating multiple partitions, each containing a portion of the data for a measure group, you can improve the access and processing performance for that measure group.
Question 29. What Are Actions, How Many Types Of Actions Are There, Explain With Example?
Answer :
Actions are powerful way of extending the value of SSAS cubes for the end user. They can click on a cube or portion of a cube to start an application with the selected item as a parameter, or to retrieve information about the selected item.
One of the objects supported by a SQL Server Analysis Services cube is the action. An action is an event that a user can initiate when accessing cube data. The event can take a number of forms. For example, a user might be able to view a Reporting Services report, open a Web page, or drill through to detailed information related to the cube data
Analysis Services supports three types of actions..
Question 30. What Are Kpis And What Is Its Use?
Answer :
In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. We use KPI to see the business at the particular point, this is represents with some graphical items such as traffic signals,ganze etc
Question 31. What Are Calculated Members And What Is Its Use?
Answer :
Calculations are item in the cube that are eveluated at runtime
Calculated members: You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions.
Example: You can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column. Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.
Answer :
Question 33. How Many Types Of Relations Are There Between Dimension And Measure Group?
Answer :
They are six relation between the dimension and measure group, they are
Question 34. What Is Attribute?
Answer :
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.
Question 35. What Is Surrogate Key?
Answer :
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.
Question 36. What Is Measure Group, Measure?
Answer :
Question 37. What Is Role Playing Dimension With Two Examples?
Answer :
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.
Question 38. What Is Scd (slowly Changing Dimension)?
Answer :
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.
Question 39. What Are Types Of Scd?
Answer :
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
Question 40. How Will You Add A Dimension To Cube?
Answer :
To add a dimension to a cube follow these steps.
Question 41. What Is Database Dimension?
Answer :
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.
Question 42. What Is Cube Dimension?
Answer :
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
Question 43. Difference Between Database Dimension And Cube Dimension?
Answer :
Question 44. What Is Molap And Its Advantage?
Answer :
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:
Question 45. What Is Rolap And Its Advantage?
Answer :
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:
Question 46. What Is Holap And Its Advantage?
Answer :
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:
Question 47. What Are Translations And Its Use?
Answer :
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.
Question 48. What Is Hierarchy, What Are Its Types And Difference Between Them?
Answer :
A hierarchy is a very important part of any OLAP engine and allows users to drill down from summary levels hierarchies represent the way user expect to explore data at more detailed level
hierarchies is made up of multipule levels creating the structure based on end user requirements.
->years->quarter->month->week ,are all the levels of calender hierarchy
They are 2 types of hierarchies they are
Question 49. What Is Attribute Hierarchy?
Answer :
An attribute hierarchy is created for every attribute in a dimension, and each hierarchy is available for dimensioning fact data. This hierarchy consists of an “All” level and a detail level containing all members of the hierarchy.
you can organize attributes into user-defined hierarchies to provide navigation paths in a cube. Under certain circumstances, you may want to disable or hide some attributes and their hierarchies.
Question 50. What Is Use Of Attributehierarchydisplayfolder Property ?
Answer :
AttributeHierarchyDisplayFolder: Identifies the folder in which to display the associated attribute hierarchy to end users. For example if I set the property value as “Test” to all the Attributes of a dimension then a folder with the name “Test” will be created and all the Attributes will be placed into the same.
Question 51. What Is Use Of Attributehierarchyenabled?
Answer :
AttributeHierarchyEnabled: Determines whether an attribute hierarchy is generated by Analysis Services for the attribute. If the attribute hierarchy is not enabled, the attribute cannot be used in a user-defined hierarchy and the attribute hierarchy cannot be referenced in Multidimensional Expressions (MDX) statements.
Question 52. What Is Use Of Attribute Hierarchy Optimized State?
Answer :
Attribute Hierarchy Optimized State:
Determines the level of optimization applied to the attribute hierarchy. By default, an attribute hierarchy is FullyOptimized, which means that Analysis Services builds indexes for the attribute hierarchy to improve query performance. The other option, NotOptimized, means that no indexes are built for the attribute hierarchy. Using NotOptimized is useful if the attribute hierarchy is used for purposes other than querying, because no additional indexes are built for the attribute. Other uses for an attribute hierarchy can be helping to order another attribute.
Question 53. What Is Use Of Attribute Hierarchy Ordered ?
Answer :
Attribute Hierarchy Ordered: Determines whether the associated attribute hierarchy is ordered. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
Question 54. What Is The Use Of Attributehierarchyvisible ?
Answer :
AttributeHierarchyVisible : Determines whether the attribute hierarchy is visible to client applications. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
Question 55. What Are Types Of Storage Modes?
Answer :
There are three standard storage modes in OLAP applications
Question 56. What Are The Types Of Database Schema?
Answer :
They are 3 types of database schema they are
Question 57. What Is Star, Snowflake And Star Flake Schema?
Answer :
Star schema: In star schema fact table will be directly linked with all dimension tables. The star schema’s dimensions are denormalized with each dimension being represented by a single table. In a star schema a central fact table connects a number of individual dimension tables.
Snowflake: The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. In snow flake schema fact table will be linked directly as well as there will be some intermediate dimension tables between fact and dimension tables.
Star flake: A hybrid structure that contains a mixture of star(denormalized) and snowflake(normalized) schema’s.
Question 58. How Will You Hide An Attribute?
Answer :
We can hide the attribute by selecting “AttributeHierarchyVisible = False” in properties of the attribute.
Question 59. How Will You Make An Attribute Not Process?
Answer :
By selecting “ AttributeHierarchyEnabled = False”, we can make an attribute not in process.
Question 60. What Is Use Of Isaggregatable Property?
Answer :
In Analysis Service we generally see all dimension has All member. This is because of IsAggregatable property of the attribute. You can set its value to false, so that it will not show All member. Its default member for that attribute. If you hide this member than you will have to set other attribute value to default member else it will pick some value as default and this will create confusion in browsing data if someone is not known to change in default member.
Question 61. What Are Key, Name And Value Columns Of An Attribute?
Answer :
Key column of any attribute: Contains the column or columns that represent the key for the attribute, which is the column in the underlying relational table in the data source view to which the attribute is bound. The value of this column for each member is displayed to users unless a value is specified for the NameColumn property.
Name column of an attribute: Identifies the column that provides the name of the attribute that is displayed to users, instead of the value in the key column for the attribute. This column is used when the key column value for an attribute member is cryptic or not otherwise useful to the user, or when the key column is based on a composite key. The NameColumn property is not used in parent-child hierarchies; instead, the NameColumn property for child members is used as the member names in a parent-child hierarchy.
Value columns of an attribute: Identifies the column that provides the value of the attribute. If the NameColumn element of the attribute is specified, the same DataItem values are used as default values for the ValueColumn element. If the NameColumn element of the attribute is not specified and the KeyColumns collection of the attribute contains a single KeyColumn element representing a key column with a string data type, the same DataItem values are used as default values for the ValueColumn element.
Question 62. What Are The Difficulties Faced In Cube Development?
Answer :
Question 63. What Is Impersonation? What Are The Different Impersonation Options Available In Ssas?
Answer :
Impersonation allows SSAS to assume the identity/security context of the client application which is used by SSAS to perform the server side data operations like data access, processing etc. As part of impersonation, the following options are available in SSAS:
Question 64. What Is A Data Source? What Are The Different Data Sources Supported By Ssas?
Answer :
A Data Source contains the connection information used by SSAS to connect to the underlying database to load the data into SSAS during processing. A Data Source primarily contains the following information (apart from various other properties like Query timeout, Isolation etc.):
• SSAS Supports both .Net and OLE DB Providers. Following are some of the major sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.
Question 65. What Is Sql Server Analysis Services (ssas)?
Answer :
SQL Server Analysis Services (SSAS) is the On-Line Analytical Processing (OLAP) Component of SQL Server. SSAS allows you to build multidimensional structures called Cubes to pre-calculate and store complex aggregations, and also to build mining models to perform data analysis to identify valuable information like trends, patterns, relationships etc. within the data using Data Mining capabilities of SSAS, which otherwise could be really difficult to determine without Data Mining capabilities.
SSAS comes bundled with SQL Server and you get to choose whether or not to install this component as part of the SQL Server Installation.
SQL Server Analysis Services (SSAS) Related Tutorials |
|
---|---|
SQL Server 2008 Tutorial | Microsoft Entity Framework Tutorial |
LINQ Tutorial |
SQL Server Analysis Services (SSAS) Related Practice Tests |
|
---|---|
SQL Server 2000 Practice Tests | MSBI Practice Tests |
SQL Server 2008 Practice Tests | SQL Server 2005 Practice Tests |
SSRS(SQL Server Reporting Services) Practice Tests | Microsoft Entity Framework Practice Tests |
LINQ Practice Tests |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.