BIDS helper is Visual Studio add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio, and now supports SQL Server 2012 (including tabular mode) and Visual Studio 2010 (SSDT). Version 1.6 … Continue reading →
If you have a source table that has a Tinyint primary key column defined as IDENTITY and you create a Data Source View (DSV) in SSAS and add that table (usually a dimension table), that IDENTITY field will be incorrectly … Continue reading →
While using a cube in SSAS is usually a great source for reporting, it is not always the best choice. Sometimes it is better to report off of the data warehouse that the cube is built from. For example, say … Continue reading →
Here is a situation I ran into recently concerning using a fact table in SSAS. This experience may help you out if you run into something similar: A data warehouse has an advertising table that has these four fields: Product Type, Inches, … Continue reading →
As your SSAS cube gets bigger, cube processing time will become a problem. This is especially true as more and more companies want cube processing during the day instead of the usual off-hours time when no one is using the … Continue reading →
Junk dimensions are dimensions that contain miscellaneous data such as flags and indicators. When designing a data warehouse, you might come across a source system that has a bunch of yes/no indicator fields. If those fields needs to be tracked … Continue reading →
A factless fact table is a fact table that does not have any measures. It is essentially an intersection of dimensions (it contains nothing but dimensional keys). There are two types of factless tables: One is for capturing an event, … Continue reading →
These are my favorite SSAS white papers released by Microsoft, with many from SQLCAT. I’ll keep this post updated with new versions of the white papers as well as new ones that come along that I find useful: Analysis Services 2008 … Continue reading →
Dimensions are often recycled for multiple purposes within the same database. For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a … Continue reading →
A conformed dimension is a dimension that has the same meaning to every fact with which it relates. Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent … Continue reading →
A reference dimension occurs when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table. This results in a snowflake schema design. The following figure shows one fact table named InternetSales, … Continue reading →
Degenerate dimensions, also called fact dimensions, are standard dimensions that are constructed from attribute columns in fact tables instead of from attribute columns in dimension tables. This is because useful dimensional data is sometimes stored in a fact table to … Continue reading →
In SSAS, data structures do not always conform to the snowflake or star schema model where one fact is associated with a single dimension member. For example, consider the example of financial transactions in accounts that can have one or … Continue reading →
Another must read white-paper: Analysis Services 2008 R2 Performance Guide. This white paper describes how business intelligence developers can apply query and processing performance-tuning techniques to their Microsoft SQL Server 2008 R2 Analysis Services OLAP solutions. It applies to SQL Server … Continue reading →
Over the past few years I have developed a list of ways to improve performance in SSAS and in two of the tools that use it: ProClarity and PerformancePoint. Below are those performance tips along with other important items to be … Continue reading →
In SSAS, to improve query performance, it’s important to understand what happens inside analysis services when a query is run. Analysis Services is made up of two engines: Formula Engine (FE) – It is single-threaded. It processes the MDX queries, determines what data is needed … Continue reading →
In SSAS, there is an Error Configuration object that is central to the management of data integrity errors. The following picture shows the ErrorConfiguration properties for a cube: There are numerous objects where you can specify the error configuration via … Continue reading →
When building a SSAS cube, you typically have a star schema containing dimension tables and fact tables. For dimension tables, you generally model these as one table per object. Building the dimension in the ETL system involves joining the various … Continue reading →
Imagine you join a company, and there is an existing SSAS database that contains a few cubes that are working nicely. You are asked to add an enhancement to one of the cubes, so you poke around in source control for the … Continue reading →
I was having a problem getting the display folder to work for a calculation in SSAS. Turns out, it will only work if you select “Measures” as the Parent hierarchy: The display folder (“Acct Calc – Supply Chain”) shows up correctly … Continue reading →