Microsoft Technologies: SQL Server Analysis Services Interview Questions and Answers

What is Datawarehousing?

A Datawarehouse is the repository of a data and it is used for Management decision support system. Datawarehouse consists of wide variety of data that has high level of business conditions at a single point in time.

In single sentence, it is repository of integrated information which can be available for queries and analysis.

The datawarehouse is an informational environment that

· Provides an integrated and total view of the enterprise

· Makes the enterprise’s current and historical information easily available for decision making

· Makes decision-support transactions possible without hindering operational systems

· Renders the organization’s information consistent

· Presents a flexible and interactive source of strategic information

What is attribute?

An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.

What is the default Isolation level in SSAS Data Source connection string?

default Isolation is ReadCommtted

1.ReadCommtted

2.Snapshot

What is the default Maximum number of connections?

What is the default provider in SSAS?

Provider=SQLNCLI10.1

What are the member properties?

Member properties are the additional information about another attribute

Example: Product's List Price

How to disable/enable browsing to an Attribute?

--> Dimension Wizard --> Select Dimension Attributes

Enable browsing - check box option

And also disable browsing in the Dimension Designer by setting the value of an attribute's AttributeHierarchyEnabled property to False

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.

How will you make an attribute not process?
By selecting "AttributeHierarchyEnabled = False", we can make an attribute not in process.

How will you hide an attribute?

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.

How to make an attribute to aggregatable?

Specifies whether the values of the attribute members can be aggregated. The default value is True

There is property for an attribute
IsAggregatable = True/False

Purpose: 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.

What is use of AttributeHierarchyDisplayFolder property ?
AttributeHierarchyDisplayFolder: Identifies the folder in which to display the associated attribute hierarchy to end users.

For example if I set the property value as "Category" to all the Attributes of a dimension then a folder with the name "Category" will be created and all the Attributes will be placed into the same.

What is use of AttributeHierarchyOptimizedState?
AttributeHierarchyOptimizedState: 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.

What is use of AttributeHierarchyOrdered ?
AttributeHierarchyOrdered: 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.

How to create Standard Dimension?

1. Right-click on the Dimensions folder and select New Dimension. On the Welcome to the Dimension Wizard, click Next

2. Verify that Use An Existing Table is selected in the Select Creation Method page and click Next

3. In the Select Source Information, select Data Source View, Main Table, Key columns and Name Column and click Next

4. select related tables in the Select Related Tables page and click Next

5. some of the attributes are preselected in list of Avaialble attributes, and also we can select desired attributes by using check boxes in Select Dimension Attributes

6. wish to Change the name of the dimension and click Finish

What are the Dimension Methods to create a dimension using the Wizard?

There are four Dimension Methods availabe while creating a dimension using dimension wizard

1. Use an existing table

2. Generate a time table in the data source

3. Generate a time table on the server

4. Generate a non-time table in the data source

* Templates drop down

What is Attribute hierarchy?
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.

What are key, name and value columns of an attribute?

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.

What are the attribute usage options?

Describes how an attribute is used.

Regular

Key

Parent

What are the Build options available in SSAS?

project properties, select Build

1. Deployment Server

Deployment Server Edition - Developer/Enterprise/Evolution/Standard

Deployment Server Version - 10.0

2. Outputs

Output Path - bin\ (default)

Remove Passwords - True

What are the Debugging options?

Project properties - select Debugging

1. Start Action

Start Object - <Current Active Object> Ex: Product(Dimension)

What are the Deployment options?

Project properties - select Deployment

1. Options

Processing Option - Default/Do Not Process/Full

Transaction Deployment - False/True

Deployment Mode - Deploy Changes Only/Deploy All

2. Target

Server - MACHINENAME\SQLSERVER

Database - <Project Name>

What is Unknown member?

The Dimension table had a null value, these records assigned to the Unknown member

The purpose of the Unknown member is to handling errors that occur during processing. For example, if you have a Fact table that has foreign keys pointing to your dimension that does not have the associated primary keys then those rows would land under the Unknown member.

By enabling the UnknownMember property of dimension and by setting the value of the KeyErrorAction property of a cube to CovnertToUnknown, you can avoid processing errors when a fact table contains a missing or invalid key for that dimension.

The UnknownMember property has three possible values:

Visible - (default)

Hidden - set to Hidden, the dimension will contain an Unknown member, but it iwll not be visible

None - set to None, the dimension will not contain Unknown member, and processing a fact table that contains a dimension key not contained in the dimension will cause an error

What is Dimension and explain the types of dimensions in SSAS?

A dimension is organized in the form of Attributes and Hierarchies.Helps in viewing/analyzing the data from different dimensions/angles to get a better understanding of the data.

Types of Dimensions

1. Database Dimension

A dimension that exists independent of a cube is called a database dimension

2. Cube Dimension

An instance of a database dimension within a cube is called a cube dimension.

3. Linked Dimension

More than one Linked Dimension can be created from a Single Database Dimension.

4. Parent-Child Dimension

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.

Example: Employee Table, In this scenario, EmployeeID is the Member Key Column and ManagerID is the Parent Key Column.

5. Role-Playing Dimension

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: Time Dimensiond

6. Conformed Dimension

A Conformed Dimension is a Dimension which connects to multiple Fact Tables across one or more Data Marts (cubes)

Conformed Dimensions are exactly the same structure, attributes, values (dimension members), meaning and definition.

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

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

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

8. Junk Dimension

A Junk Dimension is often a collection of Unrelated Attributes like indicators, flags, codes, etc.

It is also called as a Garbage Dimension.

Junk Dimensions are usually small in size.

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

9. Slowly Changing Dimension

The Slowly Changing Dimension (SCD) concept is basically about how the data modifications are absorbed and maintained in a Dimension Table.

Type 1: where the attributes are overwritten

Type 2: history is preserved

Type 3: limited history is preserved in additional columns

10. Write back dimensions

In SSAS, we can directly write data to cube dimension and measures which will be reflected in relation data source as well.

There are two types of Writeback in SSAS.

a) Dimension Writeback

When dimension data is updated then it is called Dimension Writeback

b) Cell Writeback

When measure/ fact data is updated, it is called Cell Writeback.

Situations where Writeback will be useful?

Dimension Writeback:

· For example, In Product dimension case: you want to add new product then it can be added by using Writeback feature or you are going to start new campaign in coming Christmas or want to promotional cost related to existing campaign.

· Writeback feature is good only for small number of updates because for each DML operation one request will be sent to relational database.

· It is better to change relation DB and then reprocess cube data.

· Writeback feature provides great flexibility to users who don’t have direct access to relation DB.

Cell Writeback:

· Tuples value can be changed while viewing result set. Most prevailing example is budget or forecasting data.

· By cell Writeback, change budget or forecasting data and see effect on different matrices to analyze business. You can option to commit the change data or rollback it in cube.

What is limitations for Dimension Writeback?

· Dimension table should be from single table.

· Snowflake dimension are not supported.

· Named query and DB Views are not supported.

· In Cube Dimension, Dimension key and name columns properties should be same.

How to create a writeback dimension?

Steps to creat a writeback dimension

Step 1:Create Employee dimension from relational database

Step 2:go to the properties of employee dimension and find WriteBack property, make it True(by default False)

Step 3:Deploy and Process the Dimension

Step 4:Browse the Employee Dimension

Step 5:Click on the "Member Properties" icon which is available after "Refresh"

icon and select Show All option then it will show all columns.

Step 6:Right click on any column and select "Create Sibling" option, a blank row will get displayed

Step 7:enter mandatory data into blank row columns

Step 8:Click on the blank area (outside the table)

Step 9:go to the Database and check the table data, a new row has been created.

How to delete a record using Writeback option for dimension?

Steps to creat a writeback dimension

Step 1:Create Employee dimension from relational database

Step 2:go to the properties of employee dimension and find WriteBack property, make it True(by default False)

Step 3:Deploy and Process the Dimension

Step 4:Browse the Employee Dimension

Step 5:right click on any item and select "Delete" option then it will delete the record from relational database

Step 6:go to the database and check the table data, the specified record has

been deleted

What is writeback functionality in SSAS? In what scenarios is it useful?

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.

What is Type property of a Dimension?

Type property of a Dimension is used to specify the type of information that the Analysis Services Dimension contains.

Example: Time Dimension (Contains Years, Quarters, Months, Dates, and so on)

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

A Hierarchy is a collection of one or more related Attributes which are organized in a Parent-Child fashion.

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.

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.

What are the Process options available for dimension in SSAS?

There are five process options available

1. ProcessFull

2. ProcessData

3. ProcessIndexes

4. ProcessUpdate

5. ProcessAdd

ProcessFull

· A ProcessFull command discards all storage contents of the dimension and rebuilds them. Behind the scenes, ProcessFull executes all dimension processing jobs and performs an implicit ProcessClear on all dependent partitions.

· This kind of processing is required when a structural change has been made to an object, for example, when an attribute hierarchy is added, deleted, or renamed.

ProcessData

· ProcessData discards all storage contents of the dimension and rebuilds only the attribute and hierarchy stores and also clears partitions.

· ProcessData is the first component executed by a ProcessFull operation.

ProcessIndexes

· ProcessIndexes requires that a dimension already has attribute and hierarchy stores built it preserves the data in these stores and then rebuilds the bitmap indexes.

· ProcessIndexes is the second component of the ProcessFull operation.

ProcessUpdate

· Unlike ProcessFull, ProcessUpdate does not discard the dimension storage contents. Instead, it applies updates intelligently in order to preserve dependent partitions.

· More specifically, ProcessUpdate sends SQL queries to read the entire dimension table and then applies changes to the dimension stores.

· A ProcessUpdate can handle inserts, updates, and deletions, depending on the type of attribute relationships (rigid vs. flexible) in the dimension.

· Note that ProcessUpdate will drop invalid aggregations and indexes, requiring you to take action to rebuild the aggregations in order to maintain query performance.

· However, flexible aggregations are only dropped if a change is detected.

ProcessAdd

· ProcessAdd optimizes ProcessUpdate in scenarios where you only need to insert new members. ProcessAdd does not delete or update existing members.

· The performance benefit of ProcessAdd is that you can use a different source table or data source view named query that restrict the rows of the source dimension table to only return the new rows.

· This eliminates the need to read all of the source data. In addition, ProcessAdd also retains all indexes and aggregations (flexible and rigid).

Note: Process Add is not available for dimension processing in Management Studio, It is only available as an XMLA command.

What is MembersWithDataCaption property of dimension in Parent-Child dimension?

To display the Manager name in the hierarchy to calculate performance of the team in measures.

The instance of David Bradley where he is a parent member will be associated measures that represent his team's performance

How to create a level naming template for a parent-child hierarchy in dimension?

1. Click the Dimension Structure tab. In the Attributes pane, right-click the Employees(Parent) attribute and select Properties.
2. In the Properties window, scroll to the Parent-Child group, select the NamingTemplate property, and then click the ellipsis button

3. In the Level Naming Template dialog box, type CEO in the next to the asterisk
4. Repeat step 3 to add three more levels: Manager, Supervisor, and Employee.

The Level Naming Template dialog box should look similar to this

How to set the default member(All member) for a dimension attribute?
1. In the Attributes pane, right-click the Employees attribute and select Properties
2. In the Properties window, scroll to the Advanced group, select the DefaultMember property, and then click the ellipsis

3. In the Set Default Member dialog box, select Choose a Member to Be The Default
4. Expand the All member and select <name>

Default Member can be set by using MDX expression

What is the difference between SSAS 2005 and SSAS2008?
In 2005 its not possible to create an empty cube but in 2008 we can create an empty cube.
A new feature in Analysis Services 2008 is the Attribute Relationships tab in the Dimension Designer . to implement attribute relationship is complex in ssas 2005 we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008.

What is datawarehouse in short DWH?

The datawarehouse is an informational environment that

· Provides an integrated and total view of the enterprise

· Makes the enterprise’s current and historical information easily available for decision making

· Makes decision-support transactions possible without hindering operational systems

· Renders the organization’s information consistent

· Presents a flexible and interactive source of strategic information

Have you ever worked on performance tuning, if yes what are the steps involved in it?
We need to identify the bottlenecks to tune the performance, to overcome the bottleneck we need to following the following.

1. Avoid named queries

2. Unnecessary relationships between tables

3. Proper attribute relationships to be given

4. Proper aggregation design

5. Proper partitioning of data

6. Proper dimension usage design

7. Avoid unnecessary many to many relationships

8. Avoid unnecessary measures

9. Set AttributeHierarchyEnabled = FALSE to Attributes that is not required

10. Won’t take even single measure which is not necessary.

What is attribute relationships, why we need it?
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:

· Between the key attribute and each non-key attribute bound to columns in the main dimension table.

· Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.

· Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.

How many types of attribute relationships are there?
They are 2 types of attribute relationships they are

1. Rigid

2. Flexible

Rigid:In Rigid relationships where the relationship between the attributes is fixed, attributes will not change levels or their respective attribute relationships

Example: The time dimension. We know that month “January 2009″ will ONLY belong to Year “2009″ and it wont be moved to any other year.

Flexible :
In Flexible relationship between the attributes is changed.

Example: An employee and department. An employee can be in accounts department today but it is possible that the employee will be in Marketing department tomorrow.

What are Translations and its use?
Translation: The translation feature in analysis service allows you to display caption and attributes names that correspond to a specific language. It helps in providing GLOBALIZATION to the Cube.

In today's world, business 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.

Difference between Database dimension and Cube dimension?

· The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.

· Database dimension is created one where as Cube dimension is referenced from database dimension.

· Database dimension exists only once.where as Cube dimensions can be created more than one using ROLE PLAYING Dimensions concept.

How will you add a dimension to cube?
To add a dimension to a cube follow these steps.

1. In Solution Explorer, right-click the cube, and then click View Designer.

2. In the Design tab for the cube, click the Dimension Usage tab.

3. Either click the Add Cube Dimension button, or right-click anywhere on the work surface and then click Add Cube Dimension.

4. In the Add Cube Dimension dialog box, use one of the following steps:

5. To add an existing dimension, select the dimension, and then click OK.

6. To create a new dimension to add to the cube, click New dimension, and then follow the steps in the Dimension Wizard.

What is measure group, measure?
Measure groups : These measure groups can contain different dimensions and be at different granularity but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.

Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.

Measures : Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures:
how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.

What is surrogate key?
A surrogate key is the SQL generated key which acts like an alternate primary key for the table in database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. A primary difference between a primary key and surrogate key in few databases is that primarykey uniquely identifies a record while a Surrogatekey uniquely identifies an entity.

Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.

How many types of relations are there between dimension and measure group?
They are six relation between the dimension and measure group, they are

1. No Relationship

2. Regular

3. Refernce

4. Many to Many

5. Data Mining

6. Fact

What is regular type, no relation type, fact type, referenced type, many-to-many type with example?
No relationship: The dimension and measure group are not related.

Regular: The dimension table is joined directly to the fact table.

Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.

Many to many:The dimension table is to an intermediate fact table,the intermediate fact table is joined , in turn, to an intermediate dimension table to which the fact table is joined.

Data mining:The target dimension is based on a mining model built from the source dimension. The source dimension must also be included in the cube.

Fact table: The dimension table is the fact table.

What are calculated members and what is its use?
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.

What are KPIs and what is its use?
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

What are actions, how many types of actions are there, explain with example?
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..

Report action: Report action Returns a Reporting Services report that is associated with the cube data on which the action is based.

Drill through: Drillthrough Returns a result set that provides detailed information related to the cube data on which the action is based.

Standard: Standard has five action subtypes that are based on the specified cube data.

Dataset: Returns a mutlidimensional dataset.

Proprietary: Returns a string that can be interpreted by a client application.

Rowset: Returns a tabular rowset.

Statement: Returns a command string that can be run by a client application.

URL: Returns a URL that can be opened by a client application, usually a browser.

What is partition, how will you implement it?
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.

What are Aggregations and its use?
Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard. This wizard guides you through the following steps:

1. Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.

2. Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.

3. Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.

4. Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.

5. After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.

What is the purpose of defining an aggregation design in Analysis Services?

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

What is perspective, have you ever created perspective?
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 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.

What is deploy, process and build?
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 dimesions and cube structures

Elaborating the same is given below.

Build: Its is a used to process the data of the cube database. Build is a version of a program. As a rule, a build is a pre-release version and as such is identified by a build number, rather than by a release number. Reiterative (repeated) builds are an important part of the development process. Throughout development, application components are collected and repeatedly compiled for testing purposes, to ensure a reliable final product. Build tools, such as make or Ant, enable developers to automate some programming tasks. As a verb, to build can mean either to write code or to put individual coded components of a program together.

Deployment: During development of an Analysis Services project in Business Intelligence Development Studio, you frequently deploy the project to a development server in order to create the Analysis Services database defined by the project. This is required to test the project.

for example, to browse cells in the cube, browse dimension members, or verify key performance indicators (KPIs) formulas.

What is the maximum size of a dimension?
The maximum size of the dimension is 4 gb.

What is a cube?
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.

What is AMO?
The full form of AMO is Analysis Managament Objects. This is used to create or alter cubes from .NET code.

After creating the cube, if we added a new column to the OLTP table then how you add this new attribute to the cube?
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.

What is data mart?
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
Dependent
Independent
Logical data mart

What are the difference between data mart and data warehouse?
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.

What are the difficulties faced in cube development?
You can tell any area where you feel difficult to work. But always the best answers will be the following.

· Giving attribute relationships

· Calculations

· Giving dimension usage (many to many relationship)

· Analyzing the requirements

What is named query?

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.

Why we need named queries?

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.

How will you add a new column to an existing table in data source view?

By using named calculations we can add a new column to an existing table in the data source view. Named Calculation is explained above.

What is dimension table?

Dimension table is a table which contain attributes of measurements stored in fact tables.
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.

4. What is Fact Table?

Fact table contains the measurement of business processes, and it contains foreign keys for the dimension tables.

What is fact table?

Fact table contains the measurement of business processes, and it contains foreign keys for the dimension tables.
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

1) Foregine key to the dimesion

2) measures/facts(a numerical value that used to monitor business activity)

Example – If the business process is manufacturing of bricks

Average number of bricks produced by one person/machine – measure of the business process

What is Factless fact table?

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

What is star, snowflake and star flake schema?

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.

How to set not to show results of dimensions that are unrelated to a measure group?

IgnoreUnrelatedDimensions is a property of Measure Groups. By default its value is TRUE. There are some Dimensions that are unrelated to a Measure Group. When IgnoreUnrelatedDimensions property is TRUE, the Measure Group ignores any dimension not linked to it by showing the current value(output) for all members in the unrelated dimension. This is confusing to the Information Consumer when the cube is browsed.

To avoid this confusion, set IgnoreUnrelatedDimensions=FALSE. From BIDS, Open the cube. Go to Dimension Usage tab, right click on the Measure Group and set this property. By doing so, Analysis Services return a blank value for Dimensions that are unrelated.

What are the Aggrerate Functions?

The four categories of aggregate functions are

Additive

Pseudo-additive

Non-Additive

Semi-Additive

Aggregate Function	Category
Sum	Additive
Count	Additive
Min	Pseudo-additive
Max	Pseudo-additive
DistinctCount	Non-additive
None	Non-additive
FirstChild	Semi-additive
LastChild	Semi-additive
FirstNonEmpty	Semi-additive
LastNonEmpty	Semi-additive
AverageOfChildren	Semi-additive
ByAccount	Semi-additive

How to create Standard Actions in SSAS?

In the Cube Designer, click the Actions tab

On the Actions tab toolbar, click the New Action button

An action target is the location in the cube where the action can be executed.

An action target has a target type and a target object.

You can choose from several target types.

Attribute members

Cells

Cube

Dimension members

Hierarchy

Hierarchy members

Level

Level members

If you select Cube, the action is available for all cube objects - every dimension, hierarchy, level, and member, and every cell containing a measure.

Alternatively, you can limit an action to certain members within a dimension. If you select Dimension Members as the target types, the action is available for all members of the dimension that you specify as the target object.

Similarly, you can select Hierarchy or Level to limit the availability of the action to members of the specified hierarchy or level.

You can limit the action even further if you select a target type of Attribute Members.

Another option is to use Cells as the target type.

Name

Action Target:

Target Type

Attribute members

Cells

Cube

Dimension members

Hierarchy

Hierarchy members

Level

Level members

Target Object

Condition(Optional)

Action Content

Type:

Dataset

Proprietary

Rowset

Statement

URL

Target Type:

Action Type:

How to create Drillthrough Actions in SSAS?

Drillthrough actions provide quick access to the lowest level of detail stored in a cube.

In the Cube Designer, click the Actions tab

On the Actions tab toolbar, click the Drillthrough Action button

The target type is Cell for drillthrough action. The target type is not displayed to prevent you from changing it.

The target object must be all of the measures in the cube or all of the measures in one of the cube's measure groups.

In the Measure Group Members: select the available Measure Groups

Drillthrough Columns:

To configure the columns of data that will be returned by the action

How to create Reporting Action in SSAS?

In the Cube Designer, click the Actions tab

On the Actions tab toolbar, click the Reporting Action button

"Product" is the Reporting Action

How to increase ExternalCommandTimeout in SSAS?

· Connect to SSAS from SSMS.

· Right click on the server in SSMS and select properties.

· Select Show Advanced Properties option.

· Increase the ExternalCommandTimeout value as shown below. If you set it to 0(ZERO) then it will be treated as infinity.

All Dimension Properties?

All Dimension Attribute Properties?

Measure Group Properties?

Measure Properties?

What is MOLAP and its advantages and disadvantages?

MOLAP stands for Multi dimensional Online Analytical Processing. MOLAP is the most used storage type. It is designed to offer maximum query performance to the users. The data and aggregations are stored in a multidimensional format, compressed and optimized for performance. 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 in the form of binary files. The data inside the cube will refresh only when the cube is processed, so latency is high.

Advantages:

· Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.

· The data is compressed so it takes up less space.

· And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.

· Cube browsing is fastest using MOLAP.

Disadvantages:

· This doesn’t support REAL TIME i.e newly inserted data will not be available for analysis untill the cube is processed.

What is ROLAP and its advantages and Disadvantages?

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. When we talk about ROLAP we have to discuss about disadvantage it has. Just because of this disadvantage the usage of ROLAP storage is some thing around 5%.

Advantages:

· Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.

· Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.

· Low latency.

Disadvantages:

· Main Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multidimensional storage.

What is HOLAP and its advantages and Disadvantages?

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 will have medium query performance: not as slow as ROLAP, but not as

fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Advantages:

· HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.

· Cubes are smaller than MOLAP since the detail data is kept in the relational database.

· Processing time is less than MOLAP since only aggregations are stored in multidimensional format.

· Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Disadvantages:

· As slow as ROLAP when you try to access leaf level data.

· Need to process when new records inserted.

What is the default storage mode for both dimensions and measure groups?

The Default storage mode is MOLAP for both dimensions and mesaure groups.

How to open deployed SSAS Cube from BIDS?

· Open BIDS framework from the path Start –> All Programs –> Sql Server Version –> Business Intelligence Development Studio.

· Once BIDS open, go to File –> Open –> Analysis Services Database.

· New wizard will open where you have to provide the Server name and Cube Database name as shown below. Provide the same.

· Click OK

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

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.

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.

How to enable SSAS Usage-Based Optimization in SSAS?

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design Usage-Based Optimization you might have seen all the options disabled.

This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval. Options in Usage-Based Optimization wizard will be disabled because the queries executed on the CUBE database will not be stored anywhere by default

When there is no queries stored then this concept fails and hence all the options will be disabled. We can change the settings in order to enable the STORAGE of queries that are executing on cube and hence we can enable the options in Usage-Based Optimization wizard.

· Connect to SSAS using SSMS (Sql Server Management Studio).

· Right click on the server and select properties as shown below.

· set CreateQueryLogTable to true.

· Set the value of QueryLogConnectionString to any database where you wish to store your queries.

· Set the value of QueryLogSampling to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lower the value of this property, the higher the overhead of query logging will be on the system.

· Set the value of QueryLogTableName property to any TABLE NAME of your wish.

· Click OK.

Everything set and now try to execute some queries on the cube and check the table(given in the above steps) in the database(mentioned in the above steps). It should contain the queries you executed on the cube database.

Once the queries started logging, we are all set to configure Usage-Based Optimization for the queries. In the below pic you can see all the options are enabled.

Disabling Usage-Based Optimization:

1. Set the value of QueryLogSampling to "0". This will stop query logging.

2. Click "OK"

Advantages of Usage-Based Optimization and logging:

1. If you have an environment where users are submitting repetitive queries with similar tuples/slicers - the usage based-optimization will probably do a good job of helping design useful aggregations.

2. Very little detailed knowledge of the cube structure and querying practices is needed to create aggregations.

3. Aggregations will be designed (using BIDS or BIDS Helper utilities) based on actual user queries and not just gather requirements.

Disadvantages of Usage-Based Optimization and logging:

1. Can create overhead on the system is sampling is too frequent.

2. Resulting aggregation designs may not help as much as desired or may not help the average use-case.

3. The logging table must be maintained and kept at a manageable size:

· Any structure changes will cause analysis services to issue a delete against the logging table for any queries containing the cube being changed/affected. This can cause cube structure changes to take a long time if the logging table is extremely large.

· Utilities from BIDS (or BIDS Helper functionality) can be mislead by older log data that may no longer apply to the current usage patterns of the database.

How to deploy SSAS Project using the SQL Server Analysis Services (SSAS) Deployment Wizard?

By using the

Microsoft.AnalysisServices.Deployment.exe file from the following folder

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

How to create a new Analysis Services project from existing SSAS database?

To create a new Analysis Services project from existing SSAS database by using import wizard.

To do so start Business Intelligence Development Studio (BIDS), go to menu "File"->"New Project"->"Import Analysis Services 2008 Database".

In the dialog screen enter project name, location and solution name. Then the import Analysis Services Database wizard will start. Choose SSAS server name and the database name. After that your solution will be loaded from SSAS database.

How to create an e-mail alert when some criteria has been met after processing Analysis Service cube. Any sugestion?

Analysis Services does not have "alert" functionality. SSAS cannot send e-mail, so your best bet is to delegate that functionality somewhere else. Better to use SQL Server.

Using linked server from SQL Server to your Analysis Services

EXEC msdb.dbo.sp_send_dbmail @profile_name

, @recipients

, @query

, @subject

, @attach_query_result_as_file;

How can I start Analysis Services service from the command line?

To start Microsoft SQL Server Analysis Services from the command line please use following statement:

net start "SQL Server Analysis Services (MSSQLSERVER)"

Here MSSQLSERVER is the name of the default instance.

Can I migrate Analysis Service 2000 database to Analysis Services 2008?

Yes. Start SQL Server Management Studio 2008, connect to Analysis Services server, then right mouse click on server name and choose "Migrate database" option. This will start migratin wizard where you will be able to specify where your source Analysis Services 2000 database is and where you want to migrate your database to.

When accessing calculation tab in Business Intelligence Development Studio (BIDS) I am getting error "Unexpected error occurred:Error in application".

Most likely this error is caused by mismatched SSAS dlls: msmdlocal.dll and msmgdsrv.dll. To fix this move copy this 2 dlls from folder:

c:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\

To folder:

c:\Program Files\Common Files\System\Ole DB\

Note: Before replacing any dll make sure you backup them to safe location.

How do I move all Analysis Services databases to a different folder?

Steps for moving Analysis Services databases to a different folder on the same server:

· Start Microsoft SQL Server Management Studio, Connect to Analysis Services Server

· Right click on server name, then choose properties. Change property "DataDir" to contain new location

· Stop Analysis Services Service

· Move data to location specified in previous step

· Start Analysis Services.

· Read about related possible bug here.

Note: these steps will move all databases on one instance to a different folder.

Analysis Services 2008 gives you an option to detach databases, move them to different folder and then attach them to a server from there. To detach DB: in SQL Server Management Studio select database and then right mouse click and choose option "Detach DB". To attach DB: in SQL Server Management Studio select folder "Databases", then right mouse click and choose option "Attach DB".

What are the aggregations?

Aggregations are precalculated summaries of data from leaf cells. Aggregations improve query response time by preparing the answers before the questions are asked.

Effect of AggregationUsage

Default:

The Aggregation Design Wizard will consider whether to use the "All member" or the "leaf level members" of the attribute hierarchy if it is in the pool of eligible attributes

Full:

The attribute hierarchy "leaf level members" will be included in every aggregation design

None:

The attribute hierarchy "All members" will be included in every aggregation design.

The option is not available if the attribute hierarchy doesn't have an All member, that is, if the value of the attribute's IsAggregatable property is False

Unrestricted:

The Aggregation Design wizard will consider whether to use the All member of the leaf level members of the attribute hierarhcy whether or not it is in the pool of eligible attribute

The main difference between the two wizards is that the Aggregation Design Wizard considers all queries equally likely, whereas the Usage-Based Optimization Wizard creates aggregations designed to optimize performance based on queries selected from a log.

What are the Query Log Table Columns for Usage Based Aggregations?

MSOLAP_Database: Name of the Analysis Services database

MSOLAP_ObjectPath: Fully qulified name of the object that was queried in the

format ServerName.DatabaseName.CubeName.PartitionName

MSOLAP_User: Name of the user who executed the query

Dataset: A string of zeros and one indicating the level of each attribute

hierarchy used to provide the query results.

The Usage-Based Optimization Wizard will interpret the strin gand display the

names of the attribute hierarchies returned by a query

StartTime: Time the query began in Coordinated Universal Time(UTC)

Duration: Length of time, in milliseconds, that it took to execute the query

Note: What will happen to a Cube if we make a structural change to a cube, such as adding or removing a dimension or attribute, all of the query log entries for the cube will be deleted when the changes are deployed to the Analysis Services server

How to deploy SSAS project?

Using the SQL Server Analysis Services (SSAS) Deployment Wizard

Microsoft.AnalysisServices.Deployment.exe file from the following folder

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

Click Here for Detail Steps

What is dimension model?

Dimensional Model is a group of dimension and fact tables designed for analyzing a business process.

How to migrate from SSAS 2000 to SSAS 2008?

using the migratewizard.exe to migrate SQL Server 2008 Analysis Services destination server from SQL Server 2000 Analysis Services source server.

The file is located in the following folder

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

What is Materialized Dimension?

Materialize option : (in the define relationships)

The Materialize option is selected by default. When a Referenced relationship is materialized, the relationship between the fact records and the members of the referenced dimension becomes part to the cube's physical storage structure. this imporvies the qery performance, but may cause the cube to take long to process. If the option is not selected, only the relationship between the fact records and the intermediate dimension is stored in the cube. This means that Anaylysis services has to derive the aggregated values for the members of the referenced dimension when a query is executed, resulting in slower query performance.

In which scenario, you would like to go for materializing dimension?

Reference dimensions let you create a relationship between a measure group and a dimension using an intermediate dimension to act as a bridge between them.

When the relationship is materialized, SSAS will resolve it during processing time issuing a more complex query, during the partition processing (not dimension, I have written the right word: partition!) issuing a JOIN between the fact table and the intermediate dimension, to gather the value of the key for the final dimension from the intermediate one.

This will lead to poor processing performances.

Click Here for more details

How do we see list of available warnings in SSAS?

BIDS menu, select "Database" then select "Edit Database"

Two tabs will get displayed

1. General

2. Warnings - where you can see all warnings for Cube Design, Data Source Design, Database Design, Dimension Design, Partition and Aggregation Design

“Ignore Error” when processing cube?

Try to avoid these types of mistakes in our design.

Key not found: this is because many of the dimension tables have no primary key constraints. This prevents foreign key constraints being created on the fact tables.

When data is deleted or missing from the dimension tables, fact tables are left with many records with missing dimension key values.

Duplicate key: this is caused by the lack of primary key or unique index constrains on the dimension tables.

Null key converted to unknown: key values on the fact tables should not be left Null in general. If they truly have no values, populate them with “Unknown”, “N/A”, “Not Defined”, or something that is not Null. Null is a SQL term that represents Not Defined, so let’s translate it into something that human eyes can read.

Null key not allowed: again it’s better that we take care of the Null key value issue than letting Analysis Services guess

what we meant in the first place.

Click Here for more details

What is ProcessingGroup property?

SSAS dimension can be processed by two options: sending multiple SQL statements to relational database or sending only one SQL statement. This is controlled by property called PrcessingGroup. ProcessingGroup property has two options:

ByTable

ByAttribute (Default)

In most of the scenarios, ByAttribute option works faster than other option ByTable. ByAttribute processinggroup option sends multiple distinct clause SQL statement to relation database.

SSAS Dimension processing with ByTable option is faster than ByAttribute option in some special scenarios. When relational table size is huge and time taken by multiple SQL statement for each attribute(ByAttribute approach) is higher than single SQL statement(ByTable approach) for all attributes than there are high chances that ByTable works faster than ByAttribute.

What are the main advantages in using attribute relationship? Are there any changes in implementation of attribute relationship from SSAS 2005 to SSAS 2008?

In SSAS 2008 there ia Attribute realtionships editior which is GUI based. It allows the developer to explicity to define the realtionships between the diferent attributes in a dimension. This concept is very important to be understood while building a dimension as this has an effect on the performance of the cube. Where as in SSAS 2005 attribute relationships to be developed by manually.

An attribute relationship provides the following advantages:

• Reduces the amount of memory needed for dimension processing. This speeds up dimension, partition, and query processing.

• Increases query performance because storage access is faster and execution plans are better optimized.

• Results in the selection of more effective aggregates by the aggregation design algorithms, provided that user-defined hierarchies have been defined along the relationship paths.

What do you understand by rigid and flexible relationship? Which one is better from performance perspective?

Now, with respect to analysis services, the default value for a relationship type property is flexible, so what does it really mean for us, flexible relationships require Analysis Services to drop and re-compute any existing aggregations during incremental dimension. If we change or define relationship between two attributes to Rigid, the analysis services by default takes the relationship as fixed and does not actually go and re-compute existing relationships and in other words lets the existing relationships remain without clearing them, thereby reducing the incremental process time.

In the above, the hard lines with dark arrows are the once representing the rigid relationships and the lighter once with transparent arrows are the flexible once.

In dimension usage tab, how many types of joins are possible to form relationship between measure group and dimension?

No relationship: The dimension and measure group are not related.

Regular: The dimension table is joined directly to the fact table.

Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.

Many to many:The dimension table is to an intermediate fact table,the intermediate fact table is joined , in turn, to an intermediate dimension table to which the fact table is joined.

Data mining:The target dimension is based on a mining model built from the source dimension. The source dimension must also be included in the cube.

Fact table: The dimension table is the fact table.

How to Clear SSAS cache with an SSIS package?

SSAS - Clear SSAS cache with an SSIS package Often I see developers on different forums asking how they can clear the SSAS cache. You can achieve this by restarting SSAS, which is done quite often.

Restarting is not necessary though, it's possible to clear the cache of an SSAS database with an XMLA script. Use the script below and replace "YourSsasDatabaseId" with the ID of your SSAS database and "YourSsasCubeId" with the ID of your SSAS cube.

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>YourSsasDatabaseId</DatabaseID>
<CubeID>YourSsasCubeId</CubeID>
</Object>
</ClearCache>

Please note that you must use the ID's of the database/cube and not the name! You can find the ID's of these objects at the properties in BIDS or in SSMS.

SSIS has a 'Analysis Services Execute DDL Task' that can execute this script!

Just configure the task for your SSAS instance and copy/paste the script in the SourceDirect box as shown below

How to create Time Dimensions in SSAS

Two types of Time dimensions we can create in the SSAS

1.Generate a time table in the data source

This option creates a time dimension table in the underlying data source and populates the same with data based on the inputs provided. Off course this option requires to have “Create Object” permission in the underlying data source. The wizard then creates the time dimension from this time table.

2.Generate a time table on the server

This option generates and stores the time dimension directly on the “Analysis Server” instead of in the data source and hence known as “Server Time Dimension”. This eliminates the need to create and maintain a separate time table in the Data Warehouse database. This option may be selected in case…

The user creating the table does not have enough permission to create objects in the underlying data source.

A real time cube is created based on operational database.

The user does not want to alter the Data Warehouse and save the storage space and the maintenance efforts

And So on …

Click Here for more info

What are options available in the Processing Options tab in Change Settings?

In the Processing Options tab, shown below, there are different processing

options available:

Parallel: - Use this option to process all the objects in a single transaction.

Sequential: - Use this option to process the objects sequentially.

Writeback Table Option: - Choose the option which can be used to manage a

Writeback table. There are three options available Create, Create always and Use existing

Affected Objects: - Processing affected objects will process all the objects

that have a dependency on the selected objects.

In the Dimension Key Errors tab under change settings you will be able to use either a default error configuration or a custom error configuration.

Click Here for more info

I created Date/Time dimension, but Month level members are ordered alpabetically. How do I fix that?

Most likely your month level attribute is ordered by key or name, but key or name value is a string. That is why "April" month comes before "January". To resolve this issue you can create Month key that is numeric field, so ordering by it will return expected results.

When should I consider splitting measure group into partitions?

Recommends to have no more than 20mln rows in the single partition. So if you measure group has more than 20mln records, it is recommended to split it into partitions.

During processing I can see that SSAS read x records, but all measures in the cube return 0. What else can I check?

Things to check:

· Have you specified default member on any dimension and/or attribute? Maybe this default member does not have any data.

· Is there any attributes that have "IsAggregatable" property set to false? If so, change setting to true and check if data reappeard.

· If you loaded many records and each measure value is 0 or NULL, at the end your result will allways be 0/NULL.

· Did you bind your measures to correct source fields?

How do you create distinct count measure in the separate measure group from the same fact table?

It is recommended to have measure with aggregation type "Distinct count" in the separate measure group. But in the cube designer if fact table already has measure group associated with it, by default new measures based on that fact table with be added to the same measure group. To create new measure group you should use "New Measure..." wizard (button on the toolbar). In this wizard if you select usage "Distinct count", new measure will be added to its own measure group.

How can I hide dimension attributes/hierarchy in one cube, but leave them visible in other?
Step 1:In Business Intelligence Development studio (BIDS) open cube for which you would like to hide dimension attributes and go to "Cube Structure" tab.
Setp 2:On the left site in Dimensions window choose "Attributes" tab if you want to hide attribute and choose "Hierarchies" tab if you would like to hide hierarchy.
Step 3:Select attribute/hierarchy that you would like to hide, then in properties window change property "AttributeHierarchyVisible" to False.
Step 4:You probably also want to change "AttributeHierarchyEnabled" to False if you do not use this attribute/hierarchy in any calculations.

What are Partition Binding Types in SSAS?

There are two types of binding types

1. Table Binding

2. Query Binding

What are the DiscretizationBucketCount and DiscretizationMethod dimension properties?

DiscretizationBucketCount: Discretization is basically the process of grouping contiguous values into sets of discrete values.

Analysis Services supports several variations of attribute discretizations based on different algorithms and to enable it, DiscretizationBucketCount is one of the two properties that you have to set. As the name suggests, this property defines the number of groups that the values will be placed in.

DiscretizationMethod: This property basically determines which discretization algorithm should be applied. The values of this property are

None – Discretization would not be done.

Automatic – Automatically chooses the best grouping technique among the available values.

EqualAreas – If the distribution of contiguous values are plotted as a curve, then the areas under the curve covered by wach range would be equal.

Clusters – Uses the K-Means algorithm to find ranges on the input values.

Apart from the above algorithms present in the Dimension Editor, it is also possible to specify a user defined discretization method through Data Definition Language (DDL) by defining the boundaries of every group.

What is time intelligence? How will you implement in SSAS?

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.

http://www.msbiconcepts.com/2010/10/ssas-time-intelligence-wizard.html

What is a Partition?

· Partitions are used by Microsoft SQL Server Analysis Services to manage and store data and aggregations for a measure group in a cube.

· Every measure group has at least one partition; this partition is created when the measure group is defined.

· A partition is a container for a portion of the measure group data.

· Partitions are not seen from MDX queries;all queries reflect the whole content of the measure group, regardless of how many partitions are defined for the measure group.

· Partitions are not visible to business users of the cube.

· Each partition is stored in a separate set of files.

· A partition is a file on a hard disk that contains a subset of the data included in an Analysis Services database.

· Partitions let you spread data over multiple hard disks.

· This includes combinations of both local (stored locally on hard disk) and remote (distributed across multiple hard disks) partitions. Partitions rely on storage settings to define the format and processing schedule for the database

Local Partitions:

Local partitions are partitions that are defined, processed, and stored on one server. If you have large measure groups in a cube, you might want to partition them out so that processing occurs in parallel across the partitions. The advantage is that parallel processing provides faster execution. Because one partition processing job does not have to finish before another starts, they can run in parallel.

Remote Partitions:

Remote partitions are partitions that are defined on one server, but are processed and stored on another. If you want to distribute storage of your data and metadata across multiple servers, use remote partitions. Ordinarily, when you transition from development to production, the size of data under analysis grows several times over. With such large chunks of data, one possible alternative is to distribute that data over multiple computers. This is not just because one computer cannot hold all the data, but because you will want more than one computer processing the data in parallel.

The following are the advantages of multiple partitions:

1. Improved Performance - SSAS can query a smaller amount of data by isolating one or more partitions to query rather than the entire measure group. SSAS can also query multiple partitions in parallel.

2. Flexible Configuration - each partition has its own storage mode; e.g. MOLAP, HOLAP or ROLAP and aggregation design. You can pick the optimal storage mode and aggregation based on how often the data is queried, how much data there is, how often the data changes, etc.

3. Flexible Processing - a partition can be processed separately or in parallel; you can drop a partition without having to process the cube. If you have a partition for the latest week, month, quarter, etc. you can process just the rows from the fact table that belong in the partition as opposed to processing the entire fact table.

4. Multiple Source Tables - each partition can optionally specify its own fact table, allowing the flexibility to physically split large fact tables into multiple tables.

5. Improved Scalability - you can take advantage of processing a partition on another SSAS server.

What are the Partition Benefits?

Partition Benefits:

1. Better Query Performance: Cube partition is a powerful mechanism for improving query performance. Queries that summarize data over 10 years could take considerably longer than those that only search through the current year data. If we have proper partitions then SSAS only has to scan a small subset of data to return query results hence dramatic performance improvements compared to queries running against a cube with a single partition.

2. Minimize downtime: Cube partitioning supports reducing downtime associated with cube processing. In almost all the cases, a portion of data warehouse is volatile and needs to be processed often. However other portions are relatively static. For example, in a sales cube, we need to change the current year's data nightly, but sales from previous years might change only occasionally - in case if account for merchandise returns and exchanges. If your warehouse tracks last 10 years salesthen processing only the current partition may be 10 times quicker than processing the entire cube.

3. Aggregations benefits: The partition queried frequently could benefit from additional aggregations, which in turn could improve performance. Partition(s) that are used less can be processed less frequently with considerably fewer aggregations.

4. Customized storage and processing settings: Frequently accessed partitions might benefit from proactive caching and ROLAP storage. On the other hand, other forms of storage and processing might be better for less frequently queried partitions.

5. Distributed query and processing load: SSAS allows you to create remote partitions - a remote partition resides on a server different from its parent cube. This way the queries that affect the remote partition are processed on a server separate from its parent cube, allowing you to take advantage of additional processing power.

6. Parallel Partitions Processing: SSAS allows processing multiple partitions in parallel on a server that has multiple processors. This can further reduce the total cube processing time.

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

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.

What are the different aggregation functions available in SSAS? explain each one of them?

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.

· What is the difference between a derived measure and a calculated measure?
The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

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

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.

Problem

Whenever you are preparing for a SSAS interview, it is a usual practice to decide on the list of topics to be covered and ensure that you have prepared yourself thoroughly in each of the listed areas. However, it is always a good idea to go over a few interview questions which could be asked at the interview. This will give you an opportunity to know if you have covered all the required areas or if there is a need for covering any additional areas. In this tip series, I will be covering various SSAS questions which can help you get ready for an interview.

Solution

In this tip I will cover SSAS Interview Questions on Basic Concepts, Data Sources, and Data Source Views.

What is SQL Server Analysis Services (SSAS)?

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.

What is OLAP? How is it different from OLTP?

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.

What is a Data Source? What are the different data sources supported by SSAS?

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.

What is Impersonation? What are the different impersonation options available in SSAS?

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

What is a Data Source View?

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.

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

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

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

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.

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

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.

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

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.

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

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.

Problem

Data Warehousing (DW) and OLAP are very vast areas and are very essential areas when it comes to supporting critical business decisions. It is important to have a good understanding of the subject before attending an interview in the DW/OLAP space. Hence it is essential to understand all the major components with each one in as much detail as possible. Dimensions being one of the major aspects of OLAP/SSAS, it is good to understand some basic Dimension Concepts and Types of Dimensions, which we will outline in this tip.

Solution

In the previous tip, I have covered some of the questions on Basic Concepts, Data Sources, and Data Source Views in SQL Server Analysis Services. In this tip, I will be covering some of the basic Dimension Concepts and will discuss some of the most common types of dimensions.

What is a SQL Server Analysis Services Dimension?

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

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

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

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

Problem

Dimensions are a very important component of an OLAP Solution/Cube in SQL Server Analysis Services. Hence it is good to have a fair amount of knowledge/understanding on the Dimensions, its properties, and its sub components like Attributes, Hierarchies etc.

Solution

In the first tip of this series, I covered some of the Questions on Basic Concepts, Data Sources, and Data Source Views in SQL Server Analysis Services. And in the second tip, I covered some of the Questions on General Dimension Concepts and Types of Dimensions. In this third tip, I will be covering some of the questions on Dimensions, its properties, and its components like Attributes, Hierarchies etc. within SQL Server Analysis Services.

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

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.

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

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.

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

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.

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

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/MDXexpressions.
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.

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

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.

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

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.

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

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

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

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.

What is an Unknown Member? What is its significance?

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.

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

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.

Problem

Measures and KPIs are very important aspects of an OLAP/SSAS solution from an end user standpoint, and in general, an important aspect of any Business Intelligence application. Storage is another important aspect of SSAS from an engineering standpoint. Hence it is essential to have a fair understanding of these aspects. Check out these SQL Server Analysis Services (SSAS) interview questions.

Solution

In the first, second, and third tips of this series, I have covered questions on basic concepts, data sources, data source views, dimensions, dimension types, and components associated with dimensions like attributes, hierarchies, and some of the dimension properties. In this fourth tip, I will be covering some of the questions on measures, KPIs, actions, partitions, storage modes, etc. within SQL Server Analysis Services (SSAS).

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

What are the different Storage Modes supported by Cube Partitions?

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.

What is proactive caching in SQL Server Analysis Services?

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.

Problem

Aggregation is an important aspect of any OLAP/SSAS solution and is a major differentiating factor between OLTP and OLAP solutions. The ultimate goal of building SSAS solutions is to make the right data available to the business users in the intended format in an easy to understand, convenient, and secure way. This is where Translations, Perspectives, and Security comes into the picture. Hence it is important to understand these aspects of SQL Server Analysis Servicesbefore preparing for an interview. Check out this tip for SSAS interview questions.

Solution

In the previous tips in this series, I have covered questions on basic concepts such as Data Sources, Dimensions,Measures, Actions, and Storage. In the fifth tip of this series, I will be covering some of the questions on aggregations, translations, perspectives, security, etc.

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

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

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

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.

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

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.

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

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.

What are translations? What is its significance in SSAS?

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, business 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.

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

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.

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

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.

What is writeback functionality in SSAS? In what scenarios is it useful?

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.

Problem

Building SSAS solutions is one part of the battle when delivering BI solutions to the users. Another part of the battle is to deploy and process those solutions making them highly available and accessible via different reporting tools. Having a good understanding on these aspects of delivering SSAS solutions to the users is very important and one of the major focus areas for SSAS interviews. Read on to check out the next installment of the SSAS interview questions.

Solution

In the previous tips of this series, I covered questions on basic concepts related to Data Sources, Dimensions,Measures, Actions, Storage, Aggregations, Translations, Perspectives, and Security. In this sixth and last tip of the Analysis Services interview questions and answers series, I will be covering questions on deployment, processing, querying, and accessing SSAS solutions.

What are the deployment options available in SSAS?

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

What is processing in SSAS?

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.

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

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.

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

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.

What are the different ways to process objects in SSAS?

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.

What are the programmability options available in SSAS?

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.

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

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.

What are the different tools used for accessing cubes?

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.

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

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.

Basic MDX Questions

1. What is MDX?
MDX is a language that articulates selections, calculations, and some metadata definitions against an Online Analytical Processing (OLAP) database, and provides some capabilities for specifying how query results are to be represented.
2. Where you use MDX in SQL Server 2005 OLAP?
MDX queries and expressions in SQL Server 2005 Analysis Services are used to do the following:
• Return data to a client application from a SQL Server 2005 Analysis Services cube.
• Format query results.
• Perform cube design tasks, including the definition of calculated members, named sets, scoped assignments, and key performance indicators (KPIs).
• Perform administrative tasks, including dimension and cell security.

3. What are different APIs that support MDX?
• OLE DB for OLAP
• ADO MD,
• ADOMD.Net,
• XMLA(XML for Analysis),
• Hyperion Sybase C and Java APIs,
4. What is Tuple in SSAS?
• A tuple is the intersection of one (and only one) member taken from one or several of the dimensions in the cube. A tuple identifies (or has the potential to identify) a single Cell in the multi-dimensional matrix.
• To compose a tuple with more than one dimension, you must wrap the members in parentheses (Tuple).
• You can’t compose an empty tuple. () is not a valid tuple.

5. Why don’t a tuple can have more than one member from same dimension?
• Well, if you do, it is inevitable that the ‘tuple’ that you create will end up pointing to more than one cell.
6. What is SET in SSAS?
• A set is a collection of tuples with the same dimensionality. It may have more than one tuple, but it can also have only one tuple, or even have zero tuples, in which case it is an empty set.
• If Set has three tuples with different dimensionality then we will not call that as SET.
• The curly braces { Tuple1, Tuple2 }, are used to denote the Set

7. What are query Axes in MDX?
• Query axes specify the edges of a cell set returned by a Multidimensional Expressions (MDX) SELECT statement.
• A query can have up to 128 query axes. The first five are named axes columns, Rows, pages, sections and chapters.
• However, a query that uses axis 1 must also use axis 0, and a query that uses axis 2 must also use axes 1 and 0. You cannot skip an axis in a query-you’ll get an error.
• The axis designations can come in any order.
• For axes beyond axis(4), you must use the numbers, because there are no names for them.
8. Explain the difference between Query Axis and Slicer Axis ?
Query Axis

Query axes specify the edges of a cellset returned by a Multidimensional Expressions (MDX) SELECT statement.
The Quesry axis is defined in the SELECT clause of the SELECT statement in MDX.
Query axes—the set of hierarchies from which data is retrieved for multiple members

Slicer axis

The slicer axis filters the data returned by the Multidimensional Expressions (MDX) SELECT statement.
The slicer axis is defined in the WHERE clause of the SELECT statement in MDX.
Slicer axis—the set of hierarchies from which data is retrieved for a single member.

9. Explain the MDX SELECT Statement?
• In MDX, the SELECT statement specifies a result set that contains a subset of multidimensional data that has been returned from a cube.
• The ON keyword is used with an axis name to specify where dimensions from your database are displayed
• Each Axe is made up of either one or more tuple or a Set.
• MDX uses curly braces, { and }, to enclose a set of elements from a particular dimension or set of dimensions.
• We can separate different elements with commas (,). Element names may be enclosed by [ ] characters, and may have multiple parts separated by dot (.) characters.
• The FROM clause in an MDX query names the cube from which the data is being queried.
• A WHERE clause that optionally determines which dimension or member to use as the slicer axis that restricts the extracting of data to a specific dimension or member.
10. What is the use of colon operator in MDX Select statement?
• You can specify a set as a range of members in that order by listing two members from the same level as endpoints and putting a colon between them to mean “These members and every member between them.”

• For example
SELECT
{ [Time].[Sep,2004] : [Time].[Mar,2005] } on columns,
{ [Product].[Tools] : [Product].[Home Audio] } on rows
FROM [Sales]
WHERE ([Customer].[Lubbock, TX], [Measures].[Unit Sales])
11. How to establish cube context in MDX query?
• Every MDX query runs within a specified cube context. In the SELECT statement, the FROM clause determines the cube context.
• Although the FROM clause specifies the cube context as within a single cube, this does not have to limit you from working with data from more than one cube at a time.
• You can use the MDX LookupCube function to retrieve data from cubes outside the cube context. Additionally, functions such as the Filter function, are available that allow temporary restriction of the context while evaluating the query.
12. What is use of .Members function in MDX?
• The .Members operator takes a dimension, hierarchy, or level on its left-hand side,
and returns a set of all members associated with that metadata scope. For example, [Customer].Members results in the set of all customers
13. What is the use of .Children Function in MDX?
• The Children function returns a naturally ordered set that contains the children of a specified member. If the specified member has no children, this function returns an empty set.
14. What is use of Descendants() function and specify the Flags usage?
• Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
• Syntax :- Descendants(Member_Expression/Set_expressoin [,Level_Expression /Distance [ ,Desc_Flag ] ] )

15. What is use of NON EMPTY Keyword in MDX?
• The NON EMPTY Keyword is used to remove the entirely empty slices from the query result.
16. What precisely Dimensionality of Tuple refers to?
• The “dimensionality” of a tuple refers to the set of dimensions whose members compose it. The order in which dimensions appear in a tuple is an important part of a tuple’s dimensionality
17. Explain CrossJoin() function use in MDX?
• In many cases, you will want to take the cross-product of members (or tuples) in two different sets (that is, specify all of their possible combinations). The CrossJoin() function is the most direct way of combining the two sets in this way.
• The syntax is as follows: CrossJoin (set1, set2)
• CrossJoin() only takes two sets as inputs. If you want to take the CrossJoin() of three or more sets, such as times, scenarios, and products, you can do it by nesting calls to CrossJoin().

18. Explain the Filter() function use in MDX?
• Filter() lets you reduce a set by including in the resulting set only those elements that meet some criteria.
• Filter() takes one set and one Boolean expression as its arguments and returns that subset where the Boolean expression is true.
• For example, the expression
Filter ( { [Product].[Product Category].Members }, [Measures].[Dollar Sales] >= 500 )
19. Explain the Order() function use in MDX?
• To put the tuples in a set into a sequence based on associated data values, we need to use the Order() function.
• The syntax for the Order() function is: Order (set1, expression [, ASC DESC BASC BDESC])
20. Explain DIMENSION PROPERTIES function usage in MDX?
• DIMENSION PROPERTIES a function is used to retrieve the properties of the members in cube.

Pages

Friday, August 21, 2015

SQL Server Analysis Services Interview Questions and Answers - Part3

Problem

Solution

What is SQL Server Analysis Services (SSAS)?

What is OLAP? How is it different from OLTP?

What is a Data Source? What are the different data sources supported by SSAS?

What is Impersonation? What are the different impersonation options available in SSAS?

What is a Data Source View?

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

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

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

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

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

Problem

Solution

What is a SQL Server Analysis Services Dimension?

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

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

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

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

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

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

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

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

Problem

Solution

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

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

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

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

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

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

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

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

What is an Unknown Member? What is its significance?

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

Problem

Solution

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

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

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

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

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

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

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

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

What are the different Storage Modes supported by Cube Partitions?

What is proactive caching in SQL Server Analysis Services?

Problem

Solution

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

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

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

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

What are translations? What is its significance in SSAS?

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

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

What is writeback functionality in SSAS? In what scenarios is it useful?

Problem

Solution

What are the deployment options available in SSAS?

What is processing in SSAS?

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

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

What are the different ways to process objects in SSAS?

What are the programmability options available in SSAS?

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

What are the different tools used for accessing cubes?

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