Tips
Automating Backups for SQL Server 2005 Analysis Services Databases
SQL Server 2005 Analysis Services does not offer any straight forward way to automate backups for its databases. There are no objects in the Maintenance Wizard or Integration Services for this task. Even the Analysis Services built-in (right-click) backup option has no automation or job scheduling options. So how can you setup an automated task to back up your analysis services databases?
Build a Cube From an Existing Data Source Using SQL Server Analysis Services
We are just getting started with SQL Server Analysis Services and trying to build our first cube. We have an existing relational data source to use as our starting point. There are a number of steps involved in building an SSAS cube. Can you guide us through the steps to build a cube and provide some explanations?
Build a SQL Server Analysis Services Data Dictionary
Your SQL Server Analysis Services cube (All tips | tutorial) has a large number of dimensions, attributes, hierarchies, and measures, but the end users are not sure of the meaning of some of the items. How can you generate a data dictionary with a SQL Server Reporting Services report which describes these objects? Check out this tip to learn more.
Build Integrated SQL Server Business Intelligence Solutions with Microsoft Office
I am trying to understand the BI capabilities built in to the various components of Microsoft Office 2007 and figure out how to leverage them to create BI solutions for our business users. Can you provide an overview of the capabilities?
Built in Time Dimension and Time Intelligence in SQL Server Analysis
We are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc. Can you provide a detailed explanation of how it all works?
Controlling the use of linked objects in SQL Server Analysis Services
Linked objects in SSAS should be used very carefully, as the object resides in the original cube and just a link is created in the target cube. The risk is that when multiple teams are working on different projects, people can take a shortcut for faster development and create links out of other cubes. Linked objects has a limitation that its definition cannot be modified in the target cube design, and this can be a big bottleneck if this is not the desired design. In an environment where multiple cubes are developed in the same solution, allowing free use of linked objects opens up a risk of creating a spider web of linked objects across cubes. In this tip we will look at how to control the use of linked objects in a cube.
Edition specific SSAS development using BIDS
It can often happen, that the development environment uses a more feature rich version of SQL Server than the production environment. The main issue that arises from this kind of setup is that development team might use features that might not be available in the target environment. For example, an organization might use SQL Server Enterprise Edition in most cases and therefore the development team uses the Developer edition features of SQL Server for the development phase. But consider the case, that the solution is expected to then be deployed on Standard edition. In this case, the development team needs to identify and remove these features that are used. In this tip we will look at how to deal with this issue in BIDS for SSAS developmen
Enabling Drillthrough in Analysis Services
Analysis from a cube starts with aggregated and summarized data, followed by drill-down and drill-through analysis. In this tip we will see how to enable drill-through on a cube.
Get a Descriptive Name for SQL Server Analysis Services Profiler Event Class and Event Subclass
A customer provides an SQL Server Analysis Services trace file to analyze. You save this file into a trace table and execute T-SQL code to analyze the results. Rather than getting descriptive values for the EventClass and EventSubclass data, you get numerical values. How do you resolve these numerical values to meaningful descriptive names? Check out this tip to learn more.
Getting Started with SQL Server Analysis Services
SQL Server is packed with so many different components that finding the time to discover what is included as well as finding the time to learn about these different features is often a challenge. SQL Server is great as a relational engine, but there are two other parts of SQL Server that offer a wealth of functionality, automation and insight into your data. These two tools are Reporting Services and Analysis Services. Reporting Services has only been available for a couple of years now and I see this feature used quite often which is great that people are taking advantage of this added functionality. The other tool Analysis Services has been around since SQL Server 7.0 and I still find a limited number of companies that are using these
Handle Weeks that Overlap Years in a SQL Server Date Dimension
Our business users want to report on some fact data aggregated by week. They define a week as beginning on Sunday and ending on Saturday without regard to the year. An example query would be how many employees were hired in each of the previous four weeks? While SQL Server has the DATEPART function which has an option to give the week number in the year for any given date, the week number resets to 1 when the year changes. Can your think of an easy way to implement this type of functionality where we can determine a week number then simply add or subtract a number of weeks to come up with our range of weeks?
Handle wrong results in SSAS for unrelated dimension and measure groups
While browsing a cube, if you are using a dimension and fact that are not related, the result can be quite confusing as the results appear as repeating values across all columns and/or rows depending upon the query. The repeating values are sliced or non-sliced aggregated values of the measure. No result or a NULL result would provide a more prompt hint that the dimension and measure group might be unrelated, rather than repeating the same values. In this tip we look at how to deal with this problem.
How do you create a cross tab report using a SSAS OLAP cube
I have a need use a few different rating methods to classify my customers as Great, Very Good, Good, etc. We are using SQL Server Analysis Services and I have heard you can create a cross tab report between two members on a same dimension of a SSAS cube? Do you have any suggestions on how to implement this functionality? Check out this tip to learn more.
How To Build a Cube Without a Data Source Using SQL Server Analysis Services
We are new to SSAS and are struggling with how to get started. We don't yet have a relational data warehouse that we can use to populate a cube. When you go to build a cube there is an option to build the cube without a data source. Can you provide us with the details on how to go about doing this?
How To Define Measure Group Partitions in SQL Server Analysis Services SSAS
We are anticipating building some very large cubes and would like to get a handle on how to partition our measure groups to provide flexibility in processing and administering our cubes. Can you guide us through the steps to define multiple partitions for a measure group and provide some explanations?
How to Detach and Attach a SQL Server 2008 Analysis Services Database
While looking through the new features and improvements in SQL Server 2008 Analysis Services (SSAS), we found a potentially interesting feature that allows you to detach and attach an Analysis Services Database. In this tip we cover the basic steps to detach and attach an Analysis Services database.
How To Enable User Defined Hierarchies in SQL Server Analysis Services SSAS
I have a requirement to provide users with the capability to define their own reporting hierarchies for sales data. For instance they had a report which broke down sales by the four regional VPs then by office. Later they created regions, assigned VPs to each region, then assigned offices to each VP. Now they want to be able to create a number of other scenarios and choose which one when they run a report. Can you provide some suggestions on how to do this?
How To Implement Proactive Caching in SQL Server Analysis Services SSAS
We have chosen MOLAP storage in order to maximize the query performance of our cubes. Since we have a number of cubes we are now focused on coming up with a strategy for keeping the cubes up to date as the data in our warehouse changes frequently. Can you give us the details on how we go about implementing the Proactive Caching feature in SQL Server Analysis Services 2005?
How to reduce MDX code redundancy in SQL Server Analysis Services SSAS
To query an Analysis Services cube, MDX is used as the query language. In most business settings, one would find a set of queries that are common across a number of user query requirements. To cater to this, even with a modest size IT team, there is a good chance that the same queries are developed redundantly either within a SSAS MDX script or repetitively in an ad-hoc manner in client applications. In this tip we would look at how to reuse queries without redeveloping them over and over.
How to restore a SQL Server Analysis Services Database
One of the Junior DBA in my company approached me yesterday with a dilemma. He was assigned a task to restore an Analysis Service SQL Server 2008 Database on a development server. At first I started to tell him, but figured it would be smarter to document the steps which he needs to perform to successfully restore an analysis service database from an encrypted analysis service database backup on an SQL Server 2008 Analysis Services Instance.
How to use SSAS 2008 Warning Framework effectively
During the SSAS solution design and development phase, at one time or another you will come across a number of warnings in BIDS. Many of these might be just best practices suggestions, but some may also be critical for the build of the project. In this tip we would look at how to effectively control and use the warning framework in SSAS.
Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005
I have seen the tip Dynamically Control Data Filtering in SQL Server Reporting Services Reports which showed how to leverage a SQL Server database table to filter sales data based on the user running the report. I have a similar requirement to implement dynamic filtering except my data is in a SQL Server Analysis Services (SSAS) cube and the users query the data with a variety of different tools. How can I implement dynamic security in my case?
Limitations of Static Named Sets in SQL Server Analysis Services SSAS
Creating named sets for frequently used fields in a query, is a very common practice to facilitate easier querying of a cube by users. Developers generally overlook a very important specification when creating named sets, which is to make the named set dynamic. Static named sets have limitations and hence should be used with care. In this tip we look at how dynamic named sets can be used to overcome the limitation of static named sets.
New SQL Server Analysis Services (SSAS) Tutorial
A new tutorial about SQL Server Analysis Services has just been completed by Siddharth Mehta. The tutorial covers the basic concepts of SSAS as well as a step by step process to get started with SSAS.
Optimize a SQL Server Analysis Services Measure Group Partition for Performance
In SQL Server Analysis Services (SSAS), each measure group by default has at least one partition. When a cube is created, this partition would not have any aggregation schemes defined, so these partitions are not optimized for performance. As you know, aggregations are pre-calculated sets of data which improve query response time and calculations during query evaluation. In this tip we wil learn how to design aggregations for a partition and optimize it for performance.
Process a SQL Server Analysis Services Cube Using an XMLA Query
One of the Junior DBAs in my company wanted to know how to process a cube using an XMLA Query. In this tip we will go through the steps a database administrator needs to follow to process an Analysis Services cube using an XMLA command.
Processing an Analysis Services Cube Using SQL Server Management Studio
One of the Junior DBA in my company who is new to SQL Server Analysis Services approached me yesterday with a dilemma. He wanted to know how to process a cube Using SQL Server Management Studio. In this tip we will go through the steps which a database administrator needs to follow to process an Analysis Services cube using SQL Server Management Studio.
Remove parent as a child from a SQL Server Analysis Services SSAS parent child dimension
In a parent child dimension, when an attribute's usage type is set as Parent and browsed from a client tool, many designers as well as users may get confused base on the displayed results. For example, when you browse a parent attribute in a parent child dimension like Employee and say the parent attribute is Employees, you can browse data members from the parent node until the deepest available level of the child node. By default you will find each parent member having a child member with the same name even though this member might not exist in the data. From a user experience point of view this is not a desirable way of browsing a parent child hierarchy because it gets confusing. In this tip we will look at an example of this issue and ho
SQL Server Analysis Services Best Practices for Performance Optimization - Part 2 of 4
In the first part of this series we looked at processing performance, query performance and hardware resources for your SSAS environment. In this tip, I am going to share best practices and performance optimization techniques for source system design and network settings for your Analysis Services environment.
SQL Server Analysis Services SSAS Dimension Security Stored Procedures
I have seen the tip Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005 which showed how to define roles in a cube to limit the members of a dimension that are available to the members (Windows users and/or groups) of the role. In that tip the dimension members were specified by simply selecting them via clicking a checkbox. In my case I have a security implementation in the data source to my cube where there are a set of constantly changing rules that determine who can see what. What I need is to be able to leverage the existing security implementation in the data source from the cube. How can I do that?
SQL Server Business Intelligence Tools
One of the fastest growing segments in the SQL Server market is the Business Intelligence area. With vast amounts of data, managing the data and gaining insight has become more of a challenge. Luckily, many organizations are focusing on products to improve the insight into the data for IT professionals and users alike. This give us more time to focus on our needs and data, rather than having to build sophisticated custom products as a portion of the requirements. As such, in this tip we will outline the vendors providing Business Intelligence tools in the SQL Server market.
SSAS - Best Practices and Performance Optimization - Part 1 of 4
There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues.
SSAS - Best Practices and Performance Optimization - Part 3 of 4
In this tip series we have been discussing various techniques that can be used to optimize your SQL Server Analysis Services (SSAS) environment. In this segment we look at best practices for performance optimization for your cube design and development.
SSAS - Best Practices and Performance Optimization - Part 4 of 4
There are several aspects that can hurt performance of your Analysis Services cubes. Some problems could be on the source system, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series I am going to talk about some of the best practices which you should consider during design and development of your Analysis Services cubes.
Surface Area Configuration for SQL Server Analysis Services Using PBM
I have installed a new instance of SQL Server 2008 Analysis Services and I am unable to find the Surface Area Configuration tool that I used to use in SQL Server 2005. How can I manage the Analysis Services features in SQL Server 2008? In this tip, I explain how to manage these items in SQL 2008.
Using a Parent Child Hierarchy in SQL Server to Implement a Custom Security Scheme
I have a requirement to implement a custom security scheme where roles and the user's place in the organization hierarchy are used to determine which customers a user can access. In particular the requirements are that a sales person can only access their customers and any other role can access any customer in their level of the organization hierarchy and below. We have a simple hierarchy that is made up of regions and offices. Can you provide us with an example of how to do this?
Using the SQL Server Analysis Services (SSAS) Deployment Wizard
I have to deploy my SQL Server Analysis Services project to a test server then to a production server after testing is completed. For both of these deployments I need to provide an installation program. I am not allowed to access either of these servers. For my own testing I can deploy my project using Business Intelligence Development Studio (BIDS) and this works great. How can I create an installer that my IT team can use to perform the deployment?
Using XMLA Command to Clear Cache of a SQL Server Analysis Service Database
One of the Business Intelligence developers in my company approached me yesterday with a dilemma. He wanted to know if there was a way to clear the cache of an Analysis Services database other than by recycling the Analysis Services service in SQL Server. At first I started to tell him, but figured it would be smarter to document the same and share the information. Below is the a process that can be used for SQL Server 2005 and alter versions.
Top 10
SSAS - Best Practices and Performance Optimization - Part 4 of 4
There are several aspects that can hurt performance of your Analysis Services cubes. Some problems could be on the source system, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series I am going to talk about some of the best practices which you should consider during design and development of your Analysis Services cubes.
SSAS - Best Practices and Performance Optimization - Part 1 of 4
There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues.
Get a Descriptive Name for SQL Server Analysis Services Profiler Event Class and Event Subclass
A customer provides an SQL Server Analysis Services trace file to analyze. You save this file into a trace table and execute T-SQL code to analyze the results. Rather than getting descriptive values for the EventClass and EventSubclass data, you get numerical values. How do you resolve these numerical values to meaningful descriptive names? Check out this tip to learn more.
SSAS - Best Practices and Performance Optimization - Part 3 of 4
In this tip series we have been discussing various techniques that can be used to optimize your SQL Server Analysis Services (SSAS) environment. In this segment we look at best practices for performance optimization for your cube design and development.
SQL Server Analysis Services Best Practices for Performance Optimization - Part 2 of 4
In the first part of this series we looked at processing performance, query performance and hardware resources for your SSAS environment. In this tip, I am going to share best practices and performance optimization techniques for source system design and network settings for your Analysis Services environment.
Build a SQL Server Analysis Services Data Dictionary
Your SQL Server Analysis Services cube (All tips | tutorial) has a large number of dimensions, attributes, hierarchies, and measures, but the end users are not sure of the meaning of some of the items. How can you generate a data dictionary with a SQL Server Reporting Services report which describes these objects? Check out this tip to learn more.
Build a Cube From an Existing Data Source Using SQL Server Analysis Services
We are just getting started with SQL Server Analysis Services and trying to build our first cube. We have an existing relational data source to use as our starting point. There are a number of steps involved in building an SSAS cube. Can you guide us through the steps to build a cube and provide some explanations?
Built in Time Dimension and Time Intelligence in SQL Server Analysis
We are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc. Can you provide a detailed explanation of how it all works?
Using a Parent Child Hierarchy in SQL Server to Implement a Custom Security Scheme
I have a requirement to implement a custom security scheme where roles and the user's place in the organization hierarchy are used to determine which customers a user can access. In particular the requirements are that a sales person can only access their customers and any other role can access any customer in their level of the organization hierarchy and below. We have a simple hierarchy that is made up of regions and offices. Can you provide us with an example of how to do this?
Using the SQL Server Analysis Services (SSAS) Deployment Wizard
I have to deploy my SQL Server Analysis Services project to a test server then to a production server after testing is completed. For both of these deployments I need to provide an installation program. I am not allowed to access either of these servers. For my own testing I can deploy my project using Business Intelligence Development Studio (BIDS) and this works great. How can I create an installer that my IT team can use to perform the deployment?
Last 10
SSAS - Best Practices and Performance Optimization - Part 4 of 4
There are several aspects that can hurt performance of your Analysis Services cubes. Some problems could be on the source system, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series I am going to talk about some of the best practices which you should consider during design and development of your Analysis Services cubes.
Get a Descriptive Name for SQL Server Analysis Services Profiler Event Class and Event Subclass
A customer provides an SQL Server Analysis Services trace file to analyze. You save this file into a trace table and execute T-SQL code to analyze the results. Rather than getting descriptive values for the EventClass and EventSubclass data, you get numerical values. How do you resolve these numerical values to meaningful descriptive names? Check out this tip to learn more.
SSAS - Best Practices and Performance Optimization - Part 3 of 4
In this tip series we have been discussing various techniques that can be used to optimize your SQL Server Analysis Services (SSAS) environment. In this segment we look at best practices for performance optimization for your cube design and development.
SQL Server Analysis Services Best Practices for Performance Optimization - Part 2 of 4
In the first part of this series we looked at processing performance, query performance and hardware resources for your SSAS environment. In this tip, I am going to share best practices and performance optimization techniques for source system design and network settings for your Analysis Services environment.
Build a SQL Server Analysis Services Data Dictionary
Your SQL Server Analysis Services cube (All tips | tutorial) has a large number of dimensions, attributes, hierarchies, and measures, but the end users are not sure of the meaning of some of the items. How can you generate a data dictionary with a SQL Server Reporting Services report which describes these objects? Check out this tip to learn more.
SSAS - Best Practices and Performance Optimization - Part 1 of 4
There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues.
Enabling Drillthrough in Analysis Services
Analysis from a cube starts with aggregated and summarized data, followed by drill-down and drill-through analysis. In this tip we will see how to enable drill-through on a cube.
Optimize a SQL Server Analysis Services Measure Group Partition for Performance
In SQL Server Analysis Services (SSAS), each measure group by default has at least one partition. When a cube is created, this partition would not have any aggregation schemes defined, so these partitions are not optimized for performance. As you know, aggregations are pre-calculated sets of data which improve query response time and calculations during query evaluation. In this tip we wil learn how to design aggregations for a partition and optimize it for performance.
How do you create a cross tab report using a SSAS OLAP cube
I have a need use a few different rating methods to classify my customers as Great, Very Good, Good, etc. We are using SQL Server Analysis Services and I have heard you can create a cross tab report between two members on a same dimension of a SSAS cube? Do you have any suggestions on how to implement this functionality? Check out this tip to learn more.
New SQL Server Analysis Services (SSAS) Tutorial
A new tutorial about SQL Server Analysis Services has just been completed by Siddharth Mehta. The tutorial covers the basic concepts of SSAS as well as a step by step process to get started with SSAS.
