Partitions improve the query response of SSAS cube by narrowing the MDX query to a pre-defined subset of data. Usually cubes are developed with few basic partitions to begin with and new partitions are added over time. Partitions are created using XMLA commands. The rest of this post explains the steps to create partitions using metadata and SSIS package.
Continue ReadingThe execution plan of SSAS processing engine triggers a sequence of processing tasks based on the object’s current processed state and its dependencies with other objects. This is what happens when the SSAS cube is processed from SQL Server Management Studio (SSMS). It is possible to replicate this execution plan close enough in an SSIS package so that the cube is always processed and refreshed with latest data.
Continue ReadingWhile SSAS allows you to build a cube straight of the fact and dimension tables, however there are benefits in creating the cube from database views that sits on top of the underlying fact and dimension tables. Here’s why. Continue reading
In a data warehouse, Periodic Snapshot table is a type of fact table that “freezes” the measure value at recurring reporting periods. This enables reporting of measures AS AT a point in time. Periodic Snapshots are usually created from Transaction Fact tables that support reporting FOR the period. Using MDX it is possible to create snapshot values for measures from transaction fact without creating Periodic Snapshot tables.
Continue ReadingSo you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes. Continue Reading
SSAS provides Process Incremental as one of the processing options for cubes, measure groups and partitions. It is important to understand how Process Incremental works because it differs significantly from the seemingly equivalent Process Update for dimensions. Continue Reading
[Analysis Services Execute DDL Task] Error: Internal error: An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’).
My guess is that it’s most likely an unhandled exception from the product. I recently encountered this exception while processing index of a particular SSAS Cube using SSIS Analysis Services Processing Task. The cube did get processed in a previous step and only the process index step failed, the cube was still available for browsing minus the indexes.
‘A fact relationship cannot be defined. The dimension and measure group must be based on the same table in the same data source view.’
If you get the above message when defining the relationship type as Fact between the measure group and Degenerate/Fact dimension, what could possibly go wrong especially when the second part of the message clearly tells what needs to happen? Continue Reading
Some coding tips when using Analysis Management Objects (AMO) methods
Continue Reading
Analysis Management Objects (AMO) opens up the object model of SSAS cube for programming. With a bit of .NET programing, AMO can be effectively used to automate administration tasks. Securing the cube is one such task that can be easily automated using AMO. The rest of this post explains how to automate cube security using AMO. Continue Reading
Benny Austin 