ssas-faqs: SSAS

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

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.

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

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

ProcessFull
ProcessData
ProcessIndexes
ProcessUpdate
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.

Avoid named queries
Unnecessary relationships between tables
Proper attribute relationships to be given
Proper aggregation design
Proper partitioning of data
Proper dimension usage design
Avoid unnecessary many to many relationships
Avoid unnecessary measures
Set AttributeHierarchyEnabled = FALSE to Attributes that is not required
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

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

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.

Difference between Database dimension and Cube dimension?

The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.
Database dimension is created one where as Cube dimension is referenced from database dimension.
Database dimension exists only once.where as Cube dimensions can be created more than one using ROLE PLAYING Dimensions concept.

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

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

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

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

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

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

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

What is 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

No Relationship
Regular
Refernce
Many to Many
Data Mining
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:

Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.

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

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.

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:

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

Advantages of Usage-Based Optimization and logging:

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.
Very little detailed knowledge of the cube structure and querying practices is needed to create aggregations.
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:

Can create overhead on the system is sampling is too frequent.
Resulting aggregation designs may not help as much as desired or may not help the average use-case.
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?

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.

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:

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.
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.
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.
Multiple Source Tables - each partition can optionally specify its own fact table, allowing the flexibility to physically split large fact tables into multiple tables.
Improved Scalability - you can take advantage of processing a partition on another SSAS server.

What are the Partition Benefits?

Partition Benefits:

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

SSAS

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

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 levels where security can be implemented in SSAS?

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

2 comments:

Total Pageviews