SQL Server Analysis and Tabular Services (SSAS) Interview QA

Here are the list of questions and answers that can help you prepare for your interview for sql server analysis services(SSAS). Remember to check on this page regularly as it gets updated continuously with more questions and answers. 

Q. 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.

Q. What is OLAP? How is it different from OLTP?

Answer:

  • OLAP stands for On-Line Analytical Processing. It is a capability or a set of tools which enables the end users to easily and effectively access the data warehouse data using a wide range of tools like Microsoft Excel, Reporting Services, and many other 3rd party business intelligence tools.
  • OLAP is used for analysis purposes to support day-to-day business decisions and is characterized by less frequent data updates and contains historical data. Whereas, OLTP (On-Line Transactional Processing) is used to support day-to-day business operations and is characterized by frequent data updates and contains the most recent data along with limited historical data based on the retention policy driven by business needs.

Q. 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.):
    • Provider
    • Server Name
    • Database Name
    • Impersonation Information
  • 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.

Q. 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:
    • Use a specific Windows user name and password: This option lets you to specify Windows account credentials which will be used by SSAS to perform operations like source data access, processing etc.
    • Use the service account: When this option is selected, SSAS uses the credentials of the service account under which the Analysis Services service is configured/running for source data access, processing etc.
    • Use the credentials of the current user: When this option is set, SSAS uses the credentials of the current user for performing operations like DMX Open Queries, Local cubes etc. This option cannot be used for performing server side operations like source data access, processing etc.
    • Inherit: This option let’s the SSAS server decide which impersonation mode is suitable for each type of operation. When this option is set, by default SSAS will use the service account for operations like processing and the credentials of the current user for operations like Local cubes, querying the data mining models, etc…

Q. What is a Data Source View?

Answer:

  • A Data Source View (DSV) is a logical view of the underlying database schema and offers a layer of abstraction for the underlying database schema. This layer acts as a source for SSAS and captures the schema related information from the underlying database. The schematic information present in DSV includes the following:
    • Underlying database Table(s)/View(s) metadata
    • Primary Key & Foreign Key relationships between the underlying database Table(s)
    • Additional columns in the form of Named Calculations
    • Complex logic on the underlying Table(s)/View(s) in the form of Named Queries
  • SSAS can only see the schematic information present in the DSV and it cannot see the schematic information from the underlying database.

Q. What is a Named Calculation? In what scenarios do you use it?

Answer:

  • A Named Calculation is a new column added to a Table in DSV and is based on an expression. This capability allows you to add an extra column into your DSV which is based on one or more columns from underlying data source Table(s)/View(s) combined using an expression without requiring the addition of a physical column in the underlying database Table(s)/View(s).
  • The expression used in the Named Calculation should conform to the underlying data source dialect. For example, if the underlying data source is SQL Server, then it should conform to T-SQL, If is it Oracle, then it should conform to PL/SQL, etc…
  • Named Calculations can be used in many scenarios, following are some of the common scenarios:
    • For creating Derived Columns. Say you have First Name and Last Name in the underlying data source Table/View and you want to get the Full Name as “First Name + space + Last Name”. Such things can be added as a Named Calculation.
    • For performing Lookup Operations. Say you have an Employee table which has AddressID and an Address table in which AddressID is the Primary Key. Now, to get the address information (say Country) into the Employee table in DSV, a Named Calculation can be added to the Employee table with the following expression:

(  SELECT Country  FROM Address  WHERE AddressID = Employee.AddressID)

Q. What is a Named Query? In what scenarios do you use it?

Answer:

  • A Named Query is a SQL query/expression in your DSV which acts as a Table. It is used to combine data from one or more Table(s)/View(s) from the underlying data source without requiring any schematic changes to the underlying data source Table(s)/View(s).
  • The SQL Query used in the Named Query should conform to the underlying data source dialect. For example, if the underlying data source is SQL Server, then it should conform to T-SQL, If is it Oracle, then it should conform to PL/SQL, etc…
  • Named Queries are used in various scenarios, following are some of the common scenarios:
    • Combining data from multiple Tables/Views from the underlying data source by using either simple or complex join operations.
    • Adding filter conditions for filtering out unwanted data or selecting the required data (limiting the data).
  • Pretty much everything that can be done using a Named Calculation can also be done using a Named Query.

Q. What are the pros and cons of using Tables and Named Queries in DSV?

Answer:

  • Following are some of the pros and cons of using Tables and Named Queries in DSV. Tables in the below comparison refers to the Table in DSV which references a single Table or a View in the underlying source database.

 

Tables Named Queries
Named Calculations can be added to Tables in DSV. Named Calculations cannot be added to Named Queries in DSV.
Keys and Relationships are automatically set (by the wizard) based on the Keys and Relationships in the underlying database schema. Keys and Relationships have to be set explicitly in the DSV.
Only one Table/View from the underlying data source can be referenced in DSV. More than one Table/View from the underlying data source can be referenced using a SQL Expression in the DSV.
Any filter/limiting conditions cannot be applied on a table in DSV.

Filter/limiting conditions can be applied as part of the SQL expression in the Named Query in the DSV.

 

  • Although Named Calculations and Named Queries can be used to extend the functionality of SSAS to address the evolving business needs, it is always a good practice to first build a good Dimensional Model in the beginning of a Data Warehousing/SSAS project.

Q. What is the purpose of setting Logical Keys and Relationships in DSV?

Answer:

  • Many of the user interfaces/designers/wizards in BIDS which are part of a SSAS project depend on the Primary Key and Relationships between Fact and Dimension tables. Hence it is important to define the Primary Key and Relationships in DSV.
  • By default, the Data Source View Wizard detects the Physical Primary Keys and Relationships between the tables in the underlying source database and applies the same Keys and Relationships in DSV layer. However, Logical Keys and Relationships need to be defined explicitly in the following scenarios:
    • If the DSV table is referring to an underlying database View.
    • If the DSV table is created as a Named Query.
    • If any additional relationships need to be defined in the DSV layer apart from the ones that are physically defined in the underlying source database.

Q. Is it possible to combine data from multiple data sources in SSAS? If yes, how do you accomplish it?

Answer:

  • SSAS allows combining data from multiple underlying data sources into a single DSV. To be able to add Table(s)/View(s) from multiple data sources, first you need to create a DSV using your first source and this source acts as the primary data source. Now after the initial DSV is created, you can add one or more data sources into DSV which will act as secondary data sources and you can choose additional Table(s)/View(s) from the secondary data sources which you want to include in your DSV.
  • The key thing while combining data from multiple data sources is that the Primary Data Source must support OPENROWSET queries. Hence in most cases, SQL Server is used as the Primary Data Source.

Q. What are Database Dimension and Cube Dimension? What is the difference between them?

Answer:

A Database Dimension is a Dimension which exists independent of a Cube. You can see a Database Dimension as a separate entity/file in the Solution Explorer/Project Folder. An analogy, this is pretty similar to a Class in a Programming Language.

A Cube Dimension is an Instance of a Database Dimension. An analogy, this is pretty similar to an Object (Instance of a Class) in a Programming Language.

Here are some of the highlights/differences of Database and Cube Dimensions:

  • A Database Dimension is independent of a Cube, but a Cube Dimension is always a part of a Cube.
  • One Database Dimension can be added to a Cube more than once (Role-Playing Dimension) as different instances. In this scenario, each Instance is treated as a separate Cube Dimension.
  • A Database Dimension can be added to more than one Cube, whereas a Cube Dimension can belong to a Single Cube at any given point.

Q. What is a Linked Dimension? In what scenarios do you use it?

Answer:

A Linked Dimension is a Dimension which is based on (Linked To) another Database Dimension which might be either located on the same Analysis Services server as the Linked Dimension or on a different Analysis Services server.

Linked Dimensions can be used when the exact same dimension can be used across multiple Cubes within an Organization like a Time Dimension, Geography Dimension etc.

Here are some of the highlights of a Linked Dimension:

  • More than one Linked Dimension can be created from a Single Database Dimension.
  • These can be used to implement the concept of Conformed Dimensions.
  • For an end user, a Linked Dimension appears like any other Dimension.

Q. What are the different ways to create a Time Dimension in Analysis Services?

Answer:

Time Dimension is one of the most important and most common type of dimensions as pretty much every metric is analyzed over time. Analysis Services offers following different ways to create a Time Dimension:

  • Create Using an Existing Table: This is one of the common approaches for building a Time Dimension. In this approach, a table is created in the underlying data source and pre-populated with data and it is then used to create the Time Dimension in SSAS.
  • Generate a Time Table in the Data Source: In this approach there will be no underlying table and at the time of creation of a Time Dimension in SSAS, Analysis Services creates a table in the underlying data source (relational database) and populates it with the required data using the date range, attributes, and calendars etc. which are specified at the time of creation of Time Dimension in Business Intelligence Development Studio. This option requires permissions to create a table in the underlying data source.
  • Generate a Time Table on the Server: In this approach there will be no underlying table and at the time of creation of a Time Dimension in SSAS, Analysis Services creates a table on the server and this table is used to generate the Time Dimension by the wizard. The Dimension created using this approach is called a Server Time Dimension.

Q. What is Type property of a Dimension? What is the purpose of setting this property?

Answer:

Type property of a Dimension is used to specify the type of information that the Analysis Services Dimension contains. Like a Time Dimension (Contains Years, Quarters, Months, Dates, and so on), Geography Dimension (Contains Geographical Information), and Accounts Dimension (Contains Accounts related information) etc. This property is set to Regular by default.

There are basically two important uses of setting this property:

  • This property is used by the Business Intelligence Wizard to assign/generate standard classification/MDX expressions.
  • This property is used by the Analysis Services Client Applications like Excel etc. to adjust the user interface/rendering format appropriately. For example, when a dimension is added to a Pivot Table in Excel, certain types of dimensions are automatically added across the columns axis (like Time Dimension) and a few other types of dimensions are added to the rows axis (Like Product Dimension) automatically.

Q. What is a Storage Mode? What are the different storage modes applicable to Dimensions?

Answer:

A Storage Mode defines the location in which the Dimension data will be stored and the format (Relational or Multidimensional) in which the data will be stored.

Following are the two Storage Modes supported by Dimensions:

  • ROLAP (Relational On-Line Analytical Processing):
    • When the Storage Mode is set to ROLAP for a Dimension, then the Dimension Data will be stored in the relational database tables.
    • This storage Mode offers effective memory usage by avoiding duplication of data compared to MOLAP Storage Mode.
    • Using this Storage Mode will result in a slowdown in the query performance compared to MOLAP Storage Mode.
  • MOLAP (Multidimensional On-Line Analytical Processing):
    • When the Storage Mode is set to MOLAP for a Dimension, then the Dimension Data will be stored in a multidimensional format in the Analysis Services/OLAP Server.
    • This Storage Mode offers poor memory usage as it involves duplication of data (first copy is the data in the underlying dimensional tables and the second copy is the data in the Dimension in the OLAP Server).
    • Using this Storage Mode will result in best query performance compared to any other Storage Modes available in SSAS.

Q. What is the difference between Attribute Hierarchy and User Defined Hierarchy?

Answer:

An Attribute Hierarchy is a Hierarchy created by SQL Server Analysis Services for every Attribute in a Dimension by default. An Attribute by default contains only two levels – An “All” level and a “Detail” level which is nothing but the Dimension Members.

A User Defined Hierarchy is a Hierarchy defined explicitly by the user/developer and often contains multiple levels. For example, a Calendar Hierarchy contains Year, Quarter, Month, and Date as its levels.

Here are some of the highlights/differences of Attribute and User Defined Hierarchies:

  • Attribute Hierarchies are always Two-Level (Unless All Level is suppressed) whereas User Defined Hierarchies are often Multi-Level.
  • By default, Every Attribute in a Dimension has an Attribute Hierarchy whereas User Defined Hierarchies have to be explicitly defined by the user/developer.
  • Every Dimension has at least one Attribute Hierarchy by default whereas every Dimension does not necessarily contain a User Defined Hierarchy. In essence, a Dimension can contain zero, one, or more User Defined Hierarchies.
  • Attribute Hierarchies can be enabled or disabled. Disable the Attribute Hierarchy for those attributes which are commonly not used to slice and dice the data during analysis, like Address, Phone Number, and Unit Price etc. Doing this will improve the cube processing performance and also reduces the size of the cube as those attributes are not considered for performing aggregations.
  • Attribute Hierarchies can be made visible or hidden. When an Attribute Hierarchy is hidden, it will not be visible to the client application while browsing the Dimension/Cube. Attribute Hierarchies for those attributes which are part of the User Defined Hierarchies, like Day, Month, Quarter, and Year, which are part of the Calendar Hierarchy, can be hidden, since the attribute is available to the end users through the User Defined Hierarchy and helps eliminate the confusion/redundancy for end users.

Q. What is an Attribute Relationship? What are the different types of Attribute Relationships?

Answer:

An Attribute Relationship is a relationship between various attributes within a Dimension. By default, every Attribute in a Dimension is related to the Key Attribute. Quite often these default Attribute Relationships need to be modified to suit the User Defined Hierarchies and other end user requirements.

There are basically two types of Attribute Relationships:

  • Rigid: Attribute Relationship should be set to Rigid when the relationship between those attributes is not going to change over time. For example, relationship between a Month and a Date is Rigid since a particular Date always belongs to a particular Month like 1st Feb 2012 always belongs to Feb Month of 2012. Try to set the relationship to Rigid wherever possible.
  • Flexible: Attribute Relationship should be set to Flexible when the relationship between those attributes is going to change over time. For example, relationship between an Employee and a Manager is Flexible since a particular Employee might work under one manager during this year (time period) and under a different manager during next year (another time period).

Q. What are KeyColumns and NameColumn properties of an Attribute? What is the different between them?

Answer:

KeyColumns is a property of an SSAS Dimension Attribute and it forms the Key (Unique) for the attribute. It can be bound to one or more columns in the underlying database table. When User Defined Hierarchies are created in the dimension (Attribute Relationships defined), setting this property becomes very critical and often requires setting this to a combination of more than one column from the Data Source View. For Example, say you have a Date Dimension and a hierarchy called Calendar Hierarchy (Year -> Quarter -> Month). Now what happens is that, Month gets repeated across different quarters and quarters get repeated across different years making the attribute as non-unique (like January can belong to Q1 of any year and similar Q1 can belong to any year). So to make the attribute unique, KeyColumns for Month should be set to something like Year and Month and similarly for Quarter should be set to Year and Quarter.

A NameColumn is a property of an SSAS Dimension Attribute and it is used to identify the column from the underlying Data Source View which provides the name of the attribute which is displayed to the end user by making it more user friendly instead of displaying the Key Column value. For Example, you might have ProductCategoryKey as 1, 2, 3, & 4, and ProductCategoryName as Bikes, Components, Clothing, & Accessories respectively. Now, NameColumn will be set to ProductCategoryName so that user sees them as Bikes, Components etc. even though the data in the background is processed/retrieved using the Key Column values as 1, 2 etc.

Here are some of the highlights/differences of KeyColumns and NameColumn properties:

  • KeyColumns property is defaulted to the Attribute itself, and the NameColumn property is defaulted to Key Column (when the KeyColumns is set to only one column).
  • Column(s) provided in the KeyColumns should be able to uniquely identify all the values of the respective attribute, whereas NameColumn need not be unique.
  • KeyColumns can contain one or more columns whereas NameColumn can contain only one column.

Q. What is an Unknown Member? What is its significance?

Answer:

An Unknown Member is a built-in member provided by SQL Server Analysis Services. It represents a Missing or Null value. Basically when a Dimension is Processed, Analysis Services populates each of the attributes with distinct values from the underlying data source and in this process, if it encounters Null value then it converts them appropriately (to 0 in case of numeric columns and to empty string in case of string columns) and marks them as Unknown Member for easy interpretation by the end user.

One of the important uses of an Unknown Member is to handle Early Arriving Facts. This is a scenario, in which the transactional record (Fact) would have come into the system but the corresponding Dimension/Contextual data is yet to come in which could be due to various reasons like Improperly Designed Data Load Process, Failure in the ETL Process, and a Technical Glitch in the transactional system causing delay in pushing the Dimension Data.

Unknown Member can be enabled or disabled (set to None) or set to Visible or Hidden based on the end user requirements.

Q. What are Dimension Translations? In what scenarios do you use them?

Answer:

Translation in SSAS is a mechanism to support Localization, in which the labels, names, and captions associated with any SSAS Object (pretty much every SSAS Object supports Translations) are translated from one language to another language based on the country/language of the user accessing the data. A Dimension Translation is same as Translation in SSAS, but in this case the labels, names, and captions associated with Dimensions, Attributes, Hierarchies, and/or Dimension Members are translated from one language to another language.

Translations are very useful in achieving higher level of adoption of the BI/Analytics system (SSAS). This will eliminate the language barriers among users from different locations/languages and presents the same information in different languages making single version of truth available to users across different geographical locations.

Here are some of the highlights of Dimension Translations:

  • There can be multiple Translations associated with a single Dimension, Attribute, Hierarchy, and Dimension Member etc.
  • The collation and language settings/information from the client application/computer is used to determine and provide the analysis services metadata/data to the client application.

 

Q. What is a SQL Server Analysis Services Dimension?

Answer:

A Dimension represents a set of contextual data about the transactional data that is stored in the fact table(s). It basically forms the story line around the data by providing context and thereby helping the users to understand and make sense out of the data.

Example: Let us say that there is a retail chain and the CFO of the chain announces that the “Total Sales is $10,00,000”. In this case, $10,00,000 is merely a number and does not provide any valuable information and does not make any sense to the user.

Now let us say that the CFO of the chain makes a slight change in the above announcement and says “Total Sales for FY 2012 is $10,00,000”. This makes some sense and provides some amount of context which in this case is sales amount mentioned is for Financial Year 2012 (FY2012). FY2012 represents a date (time) component and hence it represents a Date (Time) Dimension.

Similarly, we can go on and add additional Dimensions to this data (Fact Table/Cube) to provide more context about the data (in this scenario $10,00,000 is the data we are talking about). Say we change the above statement to something like “Total Sales of Facial Tissues for FY 2012 in California is $10,00,000”. This gives much better context compared to the previous two statements. In this statement, Facial Tissues represents a Product which introduces us to Product Dimension and California represents a Geographical Location which introduces us to Geography Dimension. In this manner, we can add as many dimensions as we want, to give better context to the data, so that end users can analyze the data from different dimensions and take more effective decisions.

Here are some highlights of Dimension(s):

  • It represents contextual information which adds context/meaning to the data being analyzed.
  • Helps in viewing/analyzing the data from different dimensions/angles to get a better understanding of the data.
  • A dimension is organized in the form of Attributes and Hierarchies.
  • Here are some examples of a Dimension:
    • Time Dimension
    • Product Dimension
    • Geography Dimension
    • Customer Dimension
    • Employee Dimension
  • Here are some examples of a Dimension Attribute
    • Year, Quarter, Month etc. in case of a Time Dimension
    • Color, Size etc. in case of a Product Dimension

Q. What are Natural Key and Surrogate Key in SQL Server Analysis Services?

Answer:

A Natural Key is a type of key in a table which uniquely identifies each record and has a business meaning attached to it.

Example: Incident/Ticket Number, SSN, Employee Email Alias, etc.

Here are some highlights of a Natural Key:

  • This can be numeric, string, a combination of both etc. depending on the type of data you are dealing with. In case of non-numeric data, JOINs are slower and occupies more space than an integer.
  • History cannot be maintained if used as a Primary Key.
  • Merging data from different source systems can be difficult when there is a conflict in the type of value coming from each source system.

A Surrogate Key is a type of key in a table which uniquely identifies each record, but has no business meaning attached to it. It is merely a value used to uniquely identify a record in a table.

Example: Identity Columns, GUID (Unique identifier), etc.

Here are some highlights of a Surrogate Key:

  • Most of the times this is a numeric value (like INT, BIGINT, etc.) and hence the JOINs are faster and occupies less space than character based data.
  • Allows for maintenance of history data (SCD Type 2) when used as a Primary Key.
  • Data coming from multiple source systems can be easily integrated (especially in the scenarios like Mergers, Acquisitions etc. in the industry).

Q. What is a Hierarchy in SQL Server Analysis Services? What are the different types of Hierarchies? Explain each one of them.

Answer:

A Hierarchy is a collection of one or more related Attributes which are organized in a Parent-Child fashion. An Attribute at a higher level is a parent of an Attribute at the next level and so on. They provide a very convenient way to perform roll up/drill down analysis and helps in rolling up and drilling down the numeric data in a very effective manner.

In analogy, it is like your Bing maps. If you want to locate a particular place and if you want to do it manually (without doing a text search) then you usually first locate the Country, then locate the State within the Country, after that locate the City within the State, and finally locate the place you are looking for within the City.

Example: A Calendar Hierarchy might contain Attributes like Year, Quarter, Month, and Day which are organized as a Hierarchy with Year as a parent of Quarter, Quarter as a parent of Month, and Month as a parent of Day.

Here are the common types of Hierarchies:

  • Natural Hierarchy: A Natural Hierarchy is a Hierarchy in which Every Child has a Single Parent. Example: A Calendar Hierarchy.
  • Balanced Hierarchy: A Balanced Hierarchy is a Hierarchy in which no matter through what path we traverse the Hierarchy, there is a Member at every level and every path has the same number of Levels. Example: A Calendar Hierarchy.
  • Unbalanced Hierarchy: An Unbalanced Hierarchy is a Hierarchy in which number of members and number of Levels varies across different branches of the Hierarchy. In this type of a Hierarchy, Leaf Level Members might belong to different Levels. Example: An Employee/Organization Hierarchy where in say 1 manager (Manager 1) has 2 or more people reporting to him and who belong to different Levels say L1 & L2.  On the other hand say there is another manager (at the same level as Manager 1) who has 2 or more people reporting to him and all of them belonging to the same Level say L1.
  • Ragged Hierarchy: A Ragged Hierarchy is a Hierarchy in which irrespective of the path you use to traverse, every path has the same number of Levels but not every level is guaranteed to have members except for the Top most Level and Bottom most Level (Leaf Level). Example: A Geography Hierarchy.

Q. What is a Slowly Changing Dimension in SQL Server Analysis Services? What are the different types of Slowly Changing Dimensions?

Answer:

The Slowly Changing Dimension (SCD) concept is basically about how the data modifications are absorbed and maintained in a Dimension Table. In an SCD the contents/members change over a period of time. Apart from the existing members which are modified, new records are added to the dimension similar to any other dimension.

Here are the major 3 types of Slowly Changing Dimensions:

  • Type 1: In this type of SCD, modifications to the dimension members are absorbed by overwriting the existing member data present in the Dimension Table. Example: Say you have an Address Dimension which contains addresses of all the employees in an organization. When the address of an employee changes, then the old data (address) in the Dimension is overwritten with the new data (address) and there is no way to get the old address from the table.
  • Type 2: In this type of SCD, modifications to the dimension members are absorbed by adding it as a new record into the Dimension Table. Meaning, the history of all the changes happening to the dimension members is maintained by creating a new record each time a dimension member is modified. The new (modified) record and the old record(s) are identified using some kind of a flag like say IsActive, IsDeleted etc. or using Start and End Date fields to indicate the validity of the record. New dimension members are inserted into the Dimension Table with appropriate flag value which indicates that this is the active/current record. Unlimited amount of history can be maintained in this type of SCD. Example: Considering the above example of an Address Dimension. When the address of an employee changes, then the old data (address) in the Dimension Table is retained as it is and the flag is updated to indicate that this is a historical record. The modified data (record) is inserted as a new record into the table and the flag is updated to indicate that this is the current record.
  • Type 3: In this type of SCD, modifications to the dimension members are absorbed with the help of additional columns in the Dimension Table. In this SCD type, the amount of history data (number of modifications) that can be maintained is limited by the number of columns (fixed) in the Dimension Table, which are used for tracking the history details. This is decided at the time of designing the Data Mart. Example: Considering the above example of an Address Dimension. When the address of an employee changes, then the old data (address) in the Dimension is retained as it is and the new address is updated in a separate column to indicate that this is the latest address.

Apart from the above listed 3 types of SCDs, there are other types like Type 0, Type 4, Type 5, & Type 6 etc. which are used rarely.

Q. What is a Parent-Child Dimension in SQL Server Analysis Services? Explain with an example.

Answer:

A Parent-Child Dimension is a Dimension in which two attributes in the same dimension are related to each other and they together define the linear relationship among the dimension members. The first attribute which uniquely identifies each dimension member is called the Member Key Attribute and the second attribute which identifies the parent of a dimension member is called a Parent Key Attribute.

Example: Consider an Employee Dimension which has EmployeeID as a Primary/Unique Key which uniquely identifies each Employee in the organization. The same Employee Dimension contains another attribute as ManagerID which identifies the Manager of an Employee and ManagerID contains one of the values present in the EmployeeID since Manager is also an Employee, hence he/she also has a record for self in the Employee Table. In this scenario, EmployeeID is the Member Key Column and ManagerID is the Parent Key Column.

Here are some highlights of a Parent-Child Dimension:

  • Both the Parent and the Child members exist in the same dimension.
  • There is a Parent-Child relationship between different members (rows) of the same dimension.
  • Parent Key Column in the dimension table is a Foreign Key Column (Can be physical or logical) which refers to the Member Key Column which is a Primary/Unique Key Column in the same dimension table.

Q. What is a Role-Playing Dimension in SQL Server Analysis Services? Explain with an example.

Answer:

A Role-Playing Dimension is a Dimension which is connected to the same Fact Table multiple times using different Foreign Keys. This helps the users to visualize the same cube data in different contexts/angles to get a better understanding and make better decisions.

Example: Consider a Time Dimension which is joined to the same Fact Table (Say FactSales) multiple times, each time using a different Foreign Key in the Fact Table like Order Date, Due Date, Ship Date, Delivery Date, etc. Essentially there is only one single physical dimension called Date Dimension. However, it is joined multiple times to the Fact Table to help the users to visualize the cube data in the context of different dates.

Here are some highlights of a Role-Playing Dimension:

  • It is a single physical Dimension Table.
  • Same Dimension Table connects to the same Fact Table multiple times using different Foreign Keys from the Fact Table.
  • When a Role-Playing Dimension is added to the cube, it appears as a different dimension (one instance for each Foreign Key to which it is joined) to the end users and hence playing multiple roles.

Q. What is a Conformed Dimension in SQL Server Analysis Services? Explain with an example.

Answer:

A Conformed Dimension is a Dimension which connects to multiple Fact Tables across one or more Data Marts (cubes). A Confirmed Dimension is physically implemented across multiple Data Marts with exactly the same structure, attributes, values (dimension members), meaning and definition. Each instance means exactly the same thing providing the exact same context irrespective of the Fact Table to which it is connected or the Data Mart to which it belongs.

Example: A Date Dimension has exactly the same set of attributes, same members and same meaning irrespective of which Fact Table it is connected to or to which Data Mart it belongs to as long as it belongs to the same organization. For instance, a Fiscal Calendar is exactly the same with same start and end dates across all the departments within an organization.

Here are some highlights of Conformed Dimensions:

  • Same Dimension joins to multiple Fact Tables or is used across multiple Data Marts.
  • It is a master dimension and is used across multiple dimensional models.
  • Each instance of Conformed Dimensions are exactly the same in every aspect including the attribute names, definitions, etc.

Q. What is a Degenerate Dimension in SQL Server Analysis Services? In what scenarios do you use it?

Answer:

A Degenerate Dimension is a Dimension which is derived out of a Fact Table and it appears to the end user as a separate/distinct Dimension, its data is actually stored in the Fact Table. It’s a Dimension table which does not have an underlying physical table of its own.

Degenerate Dimensions are commonly used when the Fact Table contains/represents Transactional data like Order Details, etc. and each Order has an Order Number associated with it, which forms the unique value in the Degenerate Dimension.

Example: Degenerate Dimensions having unique Order Numbers can be used to identify the various items sold as part of a particular order.

Here are some highlights of Degenerate Dimension:

  • It is derived from the Fact Table and does not have an underlying physical Dimension Table of its own.
  • It is also called as a Fact Dimension.
  • Since these dimensions are built on top of Fact Table, these are usually very large dimensions.
  • The attribute of a Degenerate Dimension is not a Foreign Key in the Fact Table.

Q. What is a Junk Dimension in SQL Server Analysis Services? In what scenarios do you use it?

Answer:

A Junk Dimension is often a collection of Unrelated Attributes like indicators, flags, codes, etc. This Dimension usually contains data which cannot be created as a separate Dimension as they tend to be too small (often) and are not worth having a separate Dimension.

These Dimensions can be used in various scenarios, but one of the common scenarios is when a Fact Table contains a lot of Attributes which are like indicators, flags, etc. Using Junk Dimensions, such Attributes can be removed/cleaned up from a Fact Table.

Example: It contains values like Yes/No, Pending/In Progress/Completed, Open/Resolved/Closed, etc.

Here are some highlights of a Junk Dimension:

  • It is also called as a Garbage Dimension.
  • Junk Dimensions are usually small in size.
  • It is a convenient way to consolidate many Smaller Dimensions which are not really worth having as a separate Dimension.

Q. What are Measures and Measure Groups? What is the difference between them?

Answer:

A Measure is any numeric quantity/value that represents a metric aligned to an organization’s goals. This is the value which the business users are interested in, and are interested in viewing these values from different angles and different granularity levels. A measure is also commonly called a fact. The term “measures” and “facts” are used interchangeably.

A Measure Group is a collection/group of measures which belong to the same underlying fact table. In SSAS, typically each Measure Group is tied to each one of the underlying fact tables.

A Measure is single numeric value whereas a Measure Group is a collection of measures.

Q. What are the different types of Measures? Explain each one of them with an example.

Answer:

Below are the most common types of measures/facts:

  • Fully Additive Facts: These are facts which can be added across all the associated dimensions. For example, sales amount is a fact which can be summed across different dimensions like customer, geography, date, product, and so on.
  • Semi-Additive Facts: These are facts which can be added across only few dimensions rather than all dimensions. For example, bank balance is a fact which can be summed across the customer dimension (i.e. the total balance of all the customers in a bank at the end of a particular quarter).  However, the same fact cannot be added across the date dimension (i.e. the total balance at the end of quarter 1 is $X million and $Y million at the end of quarter 2, so at the end of quarter 2, the total balance is only $Y million and not $X+$Y).
  • Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube. For example, profit margin is a fact which cannot be added across any of the dimensions. For example, if product P1 has a 10% profit and product P2 has a 10% profit then your net profit is still 10% and not 20%.  We cannot add profit margins across product dimensions. Similarly, if your profit margin is 10% on Day1 and 10% on Day2, then your net Profit Margin at the end of Day2 is still 10% and not 20%.
  • Derived Facts: Derived facts are the facts which are calculated from one or more base facts, often by applying additional criteria. Often these are not stored in the cube and are calculated on the fly at the time of accessing them. For example, profit margin.
  • Factless Facts: A factless fact table is one which only has references (Foreign Keys) to the dimensions and it does not contain any measures. These types of fact tables are often used to capture events (valid transactions without a net change in a measure value). For example, a balance enquiry at an automated teller machine (ATM). Though there is no change in the account balance, this transaction is still important for analysis purposes.
  • Textual Facts: Textual facts refer to the textual data present in the fact table, which is not measurable (non-additive), but is important for analysis purposes. For example, codes (i.e. product codes), flags (i.e. status flag), etc.

Q. What is the purpose of Dimension Usage settings? Explain different types of relationships between Facts and Dimensions.

Answer:

The Dimension Usage tab in the Cube Designer in SQL Server Business Intelligence Development Studio defines the relationship between a Cube Dimension and a Measure Group (s). A Dimension which is related to one of more Measure Groups, directly/indirectly, is called as a Cube Dimension. A Cube Dimension is an instance of a database Dimension as explained in the previous tip.

Following are the four different types of relationships between a Cube Dimension and a Measure Group:

  • Regular: In a Regular relationship, primary key column of a dimension is directly connected to the fact table. This type of relationship is similar to the relationship between a dimension and a fact in a Star Schema, and it can be based on either the physical primary key-foreign key relationship in the underlying relational database or the logical primary key-foreign key relationship defined in the Data Source View.
  • Referenced: In a Referenced relationship, primary key columns of a dimension is indirectly connected to the fact table through a key column in the intermediate dimension table. This type of relationship is similar to the indirect relationship between a dimension and a fact, through an intermediate dimension, in a Snowflake Schema.
  • Fact: In a Fact relationship, the dimension table and the fact table are one and the same. Basically a Fact Dimension or Degenerate Dimensionis created using one or more columns from the fact table and this degenerate dimension is used while defining/establishing the relationship in case of a fact relationship.
  • Many-to-Many: In a Many-to-Many relationship, a dimension is indirectly connected to a Measure Group through an intermediate fact table which joins with the dimension table. It is analogous to a scenario, where one project can have multiple project managers and one project manager can manage multiple projects.

Q. What are Calculated Members? How do they differ from Measures?

Answer:

Calculated Members are members of a measure group and are defined based on a combination of one or more base measures, arithmetic/conditional operators, numeric values, and functions, etc. For example, profit is a calculated member/calculate measure, which is defined based on various base measures like selling price, cost, price, tax amount, freight amount, etc.

The value of a measure (base measure) is stored in a cube as part of the cube processing process. Whereas the value of a calculated member/measure is calculated on the fly in response to a user request and only the definition is stored in the cube.

Q. What are Named Sets? What are the two types of Named Sets?

Answer:

A Named Set is a set of dimension members (usually a subset of dimension members) and is defined using MDX (a Multidimensional Expression). Often Named Sets are defined for improved usability by the end users and client applications. Apart from that, they can also be used for various calculations at the cube level. Similar to calculated members/measures, named sets are defined using a combination of cube/dimension data, arithmetic operators, numeric values, functions, etc. Some of the examples of Named Sets are top 50 customers, top 10 products, top 5 students, etc.

Named Sets are of two types: Static Named Sets and Dynamic Named Sets.

Static Named Sets, when defined in cube, are evaluated during cube processing process. Dynamic Named Sets are evaluated each time the query is invoked by the user.

Q. What are KPIs? What are the different properties associated with a KPI?

Answer:

KPI stands for Key Performance Indicator. A KPI is a measure of an organization’s performance in a pre-defined area of interest. KPIs are defined to align with the pre-defined organizational goals and help the business decision makers gain insights into their business performance.

Often KPIs have the following five commonly used properties:

  • Name: Indicates the name of the Key Performance Indicator.
  • Actual/Value: Indicates the actual value of a measure pre-defined to align with organizational goals.
  • Target/Goal: Indicates the target value (i.e. goal) of a measure pre-defined to align with organizational goals.
  • Status: It is a numeric value and indicates the status of the KPI like performance is better than expected, performance is as expected, performance is not as expected, performance is much lower than expected, etc.
  • Trend: It is a numeric value and indicates the KPIs trend like performance is constant over a period of time, performance is improving over a period of time, performance is degrading over a period of time, etc.

Apart from the above listed properties, most of the times, KPIs contain the following two optional properties:

  • Status Indicator: It is a graphical Indicator used to visually display the status of a KPI. Usually colors like red, yellow, and green are used or even other graphics like smiley or unhappy faces.
  • Trend Indicator: It is a graphical indicator used to visually display the trend of a KPI. Usually up arrow, right arrow, and down arrow are used.

Q. What are Actions in SSAS? What are the different types of Actions in SQL Server Analysis Services?

Answer:

Actions in SSAS allow us to extend the cube functionality and enable the users to interact with the cube. An Action in simple terms is basically an event, which can be initiated by a user/application and it can take various forms depending upon the type of Action defined.

Actions are primarily of following three types:

  • Drillthrough Actions: A Drillthrough Actionretrieves the detail level information associated with the cube data based on which the Drillthrough Action is defined.
  • Reporting Actions: A Reporting Action retrieves an SSRS report which is associated with the cube data. The command which invokes the SSRS report contains the report URL along with the report parameters.
  • Standard Actions: A Standard Action retrieves the action element associated with the cube data. Standard actions are further categorized into 5 different subcategories and the action element varies for each of these subcategories. The following are the types of Standard Actions:
    • Dataset Action: Returns a dataset to the client application and the action content is an MDX expression.
    • Proprietary Action: Performs an operation as defined by the client application. The action content for this type of action is specific to the calling client application and the client application is responsible for interpreting the meaning of the Action.
    • Rowset Action: A Rowset Action returns a Rowset to the client application. The action content is a command to retrieve the data.
    • Statement Action: The action content for this type of Action is an OLE DB command and it returns a command string to the client application.
    • URL Action: The Action Content for this type of action is an URL and it returns a URL to the client application which can be opened usually in a web browser. This is the default action.

Q. What are partitions in cubes? How do they different from table partitions at a SQL Server database level?

Answer:

A partition is physical storage space which contains either all or a portion of measure group data. Each measure group in SSAS has one partition by default.

A partition can be either bound to a table in the underlying relational database or a query pointing to the table(s) in the underlying database and has filters in it.

In terms of storage, cube partitions in SSAS and table partitions in a database are similar. Both these types of partitions are used to improve the performance. However, partitions in SSAS offer additional benefits including:

  • Each partition can be processed separately (i.e. a measure group can be split across multiple partitions, for example one partition for each year). Only the partitions in which data has been modified can be processed thereby improving the processing time of the cube.
  • Partitions provide improved manageability by allowing us to define storage mode, aggregation design, etc. at the partition level and these settings can vary between different partitions belonging to the same measure group.

Q. What are the different Storage Modes supported by Cube Partitions?

Answer:

There are primarily two types of data in SSAS: summary and detail data. Based on the approach used to store each of these two types of data, there are three standard storage modes supported by partitions:

  • ROLAP: ROLAP stands for Real Time Online Analytical Processing. In this storage mode, summary data is stored in the relational data warehouse and detail data is stored in the relational database. This storage mode offers low latency, but it requires large storage space as well as slower processing and query response times.
  • MOLAP: MOLAP stands for Multidimensional Online Analytical Processing. In this storage mode, both summary and detail data is stored on the OLAP server (multidimensional storage). This storage mode offers faster query response and processing times, but offers a high latency and requires average amount of storage space. This storage mode leads to duplication of data as the detail data is present in both the relational as well as the multidimensional storage.
  • HOLAP: HOLAP stands for Hybrid Online Analytical Processing. This storage mode is a combination of ROLAP and MOLAP storage modes. In this storage mode, summary data is stored in OLAP server (Multidimensional storage) and detail data is stored in the relational data warehouse. This storage mode offers optimal storage space, query response time, latency and fast processing times.

There are different variations of these Standard Storage Modes. Visit this msdn article for more details.

Q. What is proactive caching in SQL Server Analysis Services?

Answer:

Proactive caching is an advanced feature in SSAS and it enables a cube to reflect the most recent data present in the underlying database by automatically refreshing the cube based on the predefined settings. This feature allows the users to view the data in near real-time.

Proactive caching can be configured to refresh the cache (MOLAP cache) either on a pre-defined schedule or in response to an event (change in the data) from the underlying relational database. Proactive caching settings also determine whether the data is queried from the underlying relational database (ROLAP) or is read from the outdated MOLAP cache, while the MOLAP cache is rebuilt.

Proactive caching helps in minimizing latency and achieve high performance.

Q. What are aggregates? What is the purpose of defining an aggregation design in Analysis Services?

Answer:

Aggregates are summarized values, each of which corresponds to a combination of an attribute from each dimension and a measure group. An aggregate in SSAS is the differentiating factor between OLAP and OLTP, and is the fundamental principle of SSAS/OLAP, which offers blazing fast performance.

In theory, many people believe that SSAS stores aggregated values for every combination of each attribute from each dimension and in each measure group. However, in reality, SSAS stores only a part of all combinations and not all the possible combinations. In some scenarios it might be helpful to create certain percentage of aggregations every time the cube is processed, without leaving the decision to SSAS. This is achieved by defining aggregation design.

The Aggregation Design Wizard is used to design aggregations in SSAS and it provides the following options as part of the aggregation design process:

  • Design aggregations until estimated storage reaches “X” MB.
  • Design aggregations until performance gain reaches “X” percentage.
  • Design aggregations until the person designing the aggregations, clicks the “Stop” button.
  • Do not design aggregations at all (0% aggregation).

Q. What is Usage Based Optimization in SSAS? How is it performed?

Answer:

Usage Based Optimization is the process of defining aggregation design based on the actual queries executed by the users/applications accessing the cube, which is captured using a log.

SSAS offers a wizard called Usage Based Optimization Wizard to perform Usage Based Optimization by defining aggregation design. The Usage Based Optimization Wizard provides the following options as part of the Aggregation Design Process:

  • Design Aggregations until estimated storage reaches “X” MB.
  • Design Aggregations until performance gain reaches “X” percentage.
  • Design Aggregations until the person, designing the aggregations, clicks the “Stop” button.

As we can see, the above options offered by the Usage Based Optimization Wizard are similar to the ones offered by the Aggregation Wizard. However, in the case of the Aggregation Wizard, all possible queries are given equal importance, whereas in the case of the Usage Based Optimization Wizard, the importance is based on the actual queries being fired.

To use the Usage Based Optimization Wizard, Query Log Properties need to be configured at the SSAS Server Level for the tool to be able to log the queries fired by users for accessing the cube.

Q. What are the different aggregation functions available in SSAS? Can you explain each one of them?

Answer:

SSAS supports the following types of aggregation functions for Measures:

  • Fully Additive
    • Sum: This is the default aggregation type and calculates the sum of all the child members of a member at any level in the hierarchy, except for leaf level.
    • Count: Calculates the count of all the child members of a member at any level in the hierarchy except for the leaf level.
  • Semi Additive
    • Min: Returns the lowest value among all the child members.
    • Max: Returns the highest value among all the child members.
    • FirstChild: Returns the value of the first child of a member.
    • LastChild: Returns the value of the last child of a member.
    • FirstNonEmpty: Returns the value of the first non-empty child of a member.
    • LastNonEmpty: Returns the value of the last non-empty child of a member.
    • ByAccount: This aggregation type is applicable only if there is an Account Dimension in the cube. A Dimension is treated as an Account Dimension by SSAS, only when the Type property of the dimension is set to “Account”. This function aggregates the data based on the aggregation function set for members of an Account Dimension. If an Account Dimension does not exist, then setting this aggregation type is equivalent to No Aggregation (None).
    • AverageOfChildren: Calculates the average of values of all the non-empty child members of a member at any level of a hierarchy, except at the leaf level.
  • Non Additive
    • None: When an aggregation function is set to None, no aggregation is performed and the value of the member (both leaf and non-leaf) is returned directly.
    • DistinctCount: Returns the count of unique/distinct child member of a member at any level of a hierarchy, except for leaf level.

Q. What is Time Intelligence? How is it implemented in SSAS?

Answer:

Time Intelligence is a technique, which allows us to align the Time Dimension in SSAS with our actual Calendar, thereby making time calculations like Period to Date, Comparison across Parallel Time Periods, Cumulative aggregates, etc. very simple without the need for us to write explicit MDX queries/expressions.

For implementing Time Intelligence, the Type property of the Time Dimension should be explicitly set to “Time” and the attributes of this dimension should be set appropriately like year, month, etc.

SSAS offers a wizard called Business Intelligence Wizard which allows us to add different types of Intelligence to SSAS like Time Intelligence, Account, Intelligence, and Dimension Intelligence etc.

Q. What are translations? What is its significance in SSAS?

Answer:

Translations in SSAS allow us to bind labels/properties of those objects in SSAS which can be represented in multiple languages. In simple terms, Translations allow us to display the Labels and Captions of various SSAS objects in different languages. In SSAS both metadata and data can be translated. The objects which support Translations in SSAS include databases, cubes, dimensions, attributes, hierarchies, measure groups, calculated members, KPIs, and various other objects.

In today’s world, businesses are growing and expanding to a very large extent and tend to have presence internationally. In such situations, it would be essential that, SSAS objects support localization and people from different geographical locations be able to see the information in their local language.

Q. What are perspectives? How they can be used to implement security in SSAS?

Answer:

A perspective is a visual layer on top of SSAS and is used to display a subset of the cube/dimension based on either a specific subject area or based on the target audience, or any other scenario which might require exposing a subset of cube/dimension to the users/applications to simplify the view of data.

Perspectives are a very useful feature especially when the cubes are too large in size containing many dimensions and measure groups. Such large cubes can be too much data for the users, if the data is not exposed using suitable perspectives.

Perspectives are not a security mechanism. They cannot be used to implement security in SSAS. The security settings/restrictions applied at the cube/dimension/data level apply at the perspective level and no separate security settings can be defined at the perspective level.

Q. What are the different levels where security can be implemented in SSAS?

Answer:

Security is one of the most common and most important needs for any application especially when working with sensitive data. In SSAS, security can be implemented at various levels:

  • Server Level: A user is either allowed or denied access to the OLAP Server.
  • Database Level: A user is either allowed or denied access to specific databases on the SSAS Server.
  • Cube Level: Users can be allowed or denied access to specific cubes within a database especially when a database contains multiple cubes.
  • Dimension Level: Users are either allowed or denied access to specific dimensions and thereby enabling or restricting them from browsing the data using those specific dimensions.
  • Measure Level: Either one or more measures can be exposed or restricted from the user.
  • Cell Level: This can be of two types: Security for dimension members and security for data cells. With dimension members, users can be allowed or restricted to see specific members of a dimension. With data cells, users can be allowed or restricted to see a specific range of cells. In both of these scenarios, users can be given read, read/write, etc. permissions.

Q. What is write back functionality in SSAS? In what scenarios is it useful?

Answer:

Writeback is a functionality in SSAS which allows the users to write the data back into the cube. Meaning, while browsing the cube, they can make changes to the data and those changes are written back into the cube.

Writeback can be enabled primarily at two levels, either at the dimension and/or partition. For dimension level writeback, the users can modify the members of a dimension. For partition writeback, users are allowed to modify the cells of data and hence it is commonly referred to as cell level writeback.

Users require special permissions to be able to use the writeback functionality to write the data back into the cube. For dimension writeback, users should have the read/write permissions and the “enable dimension writeback” property should be set. In the case of cell/partition level writeback users need to have read/write permissions and the partition should be enabled for writeback.

Writeback functionality can be useful in many scenarios such as to perform What-If analysis. Say a business user wants to understand how much impact a change in freight and other taxes can have on profitability, which they can simply achieve by trying to set a different set of values for freight and taxes then see the profitability changing.

Q. What are the deployment options available in SSAS?

Answer:

Below are the different options to deploy a SSAS solution to an OLAP Server:

  • Business Intelligence Development Studio (BIDS): This is one of the primary tools used for developing SQL Server BI solutions and is the most frequently used IDE by the developer community in SQL Server 2008. BIDS can be used to deploy the SSAS Objects/Solutions.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • Analysis Services Deployment Wizard: Analysis Services Deployment Wizard is a tool included as part of the SSAS installation and can be used to deploy the SSAS objects/solutions.
  • XML for Analysis Services (XMLA): It is used for deploying the SSAS objects to the OLAP Server. Apart from deployment purposes, XMLA scripts can be used for various other purposes like managing SSAS objects, creating/modifying SSAS objects and processing SSAS objects, etc. It is also known as the Analysis Services Scripting Language (ASSL).

Q. What is processing in SSAS?

Answer:

Processing is the process to load data into SSAS objects like Cubes, Partitions, and Dimensions, etc. on the OLAP Server with the data from the underlying relational data warehouse. As part of the processing step(s), aggregations are performed, data is loaded into one or more pre-defined partitions and various actions, which involve data modifications are performed in this step. The processed data is stored into respective locations like the OLAP Server, relational data warehouse, etc. depending on the Storage Mode defined for the different objects.

Q. What is database synchronization in SSAS? What is its advantage?

Answer:

Database synchronization is the process of updating the data and metadata in a database based on the target SSAS Server based on the data and metadata from the source SSAS Server. Analysis Services offers a tool called the “Synchronize Database Wizard” to synchronize databases on two different SSAS databases.

The primary advantage of a database synchronization via the Database Synchronization Wizard is that, while the databases are being synchronized by the wizard, the destination database continues to be available and the users can access the database as usual. Once the synchronization is completed, Analysis Services drops the out dated copy (old database) and switches automatically to the new database which has been updated.

Q. What are the different processing modes available in SSAS? Explain each one of them.

Answer:

Below are the processing options available in SSAS:

  • Process Add: This processing mode is available for Dimensions, Measure Groups and Partitions. When this processing mode is applied to Dimensions, it adds new members into the dimensions from the underlying database and updates the descriptions of the attributes. When this processing mode is applied to Measure Groups and Partitions, it adds the incremental fact data into the cube and processes only the affected partitions.
  • Process Clear: This processing mode is available for Dimensions, Measure Groups, Partitions, Cubes, Mining Models and Structures as well as OLAP (SSAS) databases. This processing mode is used to remove the data from an Analysis Services object. Data is removed from an object and all the subsequent child level objects.
  • Process Clear Structure: This processing mode is available only for Mining Structures as part of the Data Mining component of SSAS. This processing mode is used to clear the training data previously loaded into a mining structure.
  • Process Data: This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This processing mode is used to process the data without actually building or creating the aggregations and indexes.
  • Process Default: This processing mode is available for Dimensions, Measure Groups, Partitions, Mining Models and Structures, Cubes, and OLAP (SSAS) databases. When this process is applied to any of these objects, it first determines the current state of the object from a processing standpoint and then does the necessary processing to bring the object to current/fully processed state.
  • Process Full: This processing mode is available for Dimensions, Measure Groups, Partitions, Mining Models and Structures, Cubes, and OLAP (SSAS) Databases. When this processing mode is applied to an object, the object itself and all the subsequent lower level objects are fully processed. The data is cleared from an object if it already has some previously processed data.
  • Process Index: This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This option works only on the processed objects and for unprocessed objects you will get an error. When applied on any processed objects, it creates aggregations and indexes if they don’t already exist. It recreates them if they already exist.
  • Process Structure: This processing mode is available for Mining Structures and Cubes. When this processing mode is applied to a cube, it creates the cube structure and it also processes the cube dimensions if they are not already processed. This processing mode loads the data into the Mining Structure, when applied on it.
  • Process Update: This processing mode is available only for Dimensions. When this processing mode is used, it freshly reads all the dimension data from the database and updates the data in the dimension.

Q. What are the different ways to process objects in SSAS?

Answer:

SSAS objects can be processed using various techniques. The following are the most popular tools and techniques used to process objects in SSAS:

  • SQL Server Integration Services (SSIS): Analysis Service Processing Task in SSIS can be used to process SSAS objects.
  • Business Intelligence Development Studio (BIDS): SSAS Objects can be processed via Business Intelligence Development Studio from within the SSAS Project.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • SQL Server Management Studio (SSMS): SQL Server Management Studio can be used in multiple ways to process SSAS objects.
    • Object Explorer: Using SSMS, one can directly connect to Analysis Services Server and process the objects through the Object Explorer (GUI).
    • XMLA: An XML for Analysis Services (XMLA) script can be executed directly in SSMS to process the SSAS objects.
    • SQL Server Agent: An XMLA script can be executed using a SQL Server Agent Job which also helps in scheduling the script to perform the processing during off hours.
  • Command Line: SSAS offers the following two command line utilities which can be used to process SSAS objects:
    • Power Shell: Power Shell is a well-known and very powerful command line utility which is part of SQL Server. It can be used to process SSAS objects. Apart from this, Power Shell can be used to perform various other operations within SQL Server.
    • ascmd: The ascmd utility can be used to execute XMLA scripts as well as MDX and DMX queries against an instance of SSAS. Using this utility, one can execute the relevant scripts to process the SSAS objects.
  • Programmatically: Analysis Management Objects (AMO) is a collection of SSAS management classes, which can be used for processing SSAS objects. Apart from this, AMO can be used for various management activities in SSAS.

Q. What are the programmability options available in SSAS?

Answer:

In Analysis Services there are two major programmability options: AMO and ADOMD.NET.

AMO (Analysis Management Objects) is a set of classes used for managing an Analysis Services instance and can be used to perform various management activities related to SSAS. Using AMO, the following operations can be performed on the SSAS objects:

  • Creation
  • Modification
  • Deletion
  • Processing

With AMO, one cannot query the SSAS objects. To query the data from SSAS programmatically, one can use ADOMD.NET which is a .NET based data provider. It is primarily used to communicate with SSAS and uses Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and Analysis Services Scripting Language (ASSL), etc. commands to query the data.

Q. What are the different querying and programming languages supported by SSAS?

Answer:

The following languages are supported by SSAS:

  • T-SQL: Transact-SQL queries can be used to query certain aspects of SSAS.
  • MDX: It stands for Multidimensional Expressions. MDX queries are the primary means of querying data in SSAS.
  • DMX: It stands for Data Mining Extensions. DMX is used for queries related to Data Mining in SSAS.
  • AMO: It stands for Analysis Management Objects. It is .NET based and can be used to manage SSAS objects, creating/modifying/deleting SSAS objects and implementing security in certain scenarios.

Q. What are the different tools used for accessing cubes?

Answer:

Here is a list of different tools which are used for accessing Cubes in Analysis Services:

  • Business Intelligence Development Studio (BIDS): BIDS is used to access the cube and preview the data at the design time. This option is useful only for the developer community and is not applicable to business analysts and decision makers.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • SQL Server Management Studio (SSMS): SSMS is used to access the cube by people in different roles. It is used to access the cube by the development/testing teams to validate the data. Those business analysts and decision makers (very few) who are familiar with SSMS; also use it for accessing the cubes.
  • Microsoft Office Excel: Excel being one of the most widely used data analysis tool, has the capability to connect to cubes and perform different kinds of reporting such as pivot tables, charts, etc. It is used by many decision makers, business analysis, and even developers for reporting purposes.
  • SQL Server Reporting Services (SSRS): SSRS allows Analysis Services databases or cubes as a data source to build reports. This option is available both in BIDS\SQL Server Data Tools while building canned reports and in Report Builder while doing ad-hoc reporting.
  • PerformancePoint Services (PPS): PPS which is now a part of SharePoint 2010 allows building KPIs, scorecards, reports, and dashboards with Analysis Services cubes as data source.

These are the major tools in the Microsoft BI stack which support Analysis Services data sources. Apart from these, there are various third party tools which support Analysis Services as a data source.

Q. How do you capture which users are connecting to SSAS, which objects of SSAS are being used, which queries are being executed, etc.?

Answer:

Analysis Services offers Dynamic Management Views (DMV), which are along the same lines as the DMVs for the relational database engine. Using the DMVs is the most common approach to capturing these statistics and to monitor the current status of the SSAS server. With the SSAS DMVs, one can capture various statistics including:

  • Utilization of resources such as CPU, memory, etc.
  • Usage of aggregations, hierarchies, and other components of SSAS.
  • Who is connecting to the OLAP database and what are the different objects being used.
  • Queries being executed by the users, time taken for executing different user queries, and so on.