This post discusses the challenges for the ETL to handle Late Arriving Dimensions/Early Arriving Facts. and proposes solutions to tackle these challenges and optimize the ETL for Late Arriving Dimensions. Continue reading
So 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? While there are benefits of having all cubes in one database there are scenarios where having one cube per database might be just as helpful. 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
This post is about troubleshooting and finding a workaround for this exception – [Analysis Services Execute DDL Task] Error: Internal error: An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’).
Continue reading
‘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 this 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
A Role Playing Dimensions reuses its definition to represent same concept in different contexts. Common example of Role Playing Dimension is date dimension. For instance, date dimension can be used to represent order date, delivery date and shipment date. Currency, staff, organisation hierarchy are other dimensions that are usually role played. While Role Playing Dimensions promote dimension reuse and share dimension data security, the effect on cube browsing must also be considered. Continue reading
SSAS Dynamic Management Views (DMV’s) are very useful to query metadata of a cube. For developers, this is quite handy to provide simple documentation of the cubes they build. Continue reading
Dataflow Discoverer (DFLD) is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting.DFLD works on one package at a time. It’s a typical ops requirement to keep the DFLMetadata of all SSIS packages up to date. In this post a technique is described to automate refresh of dataflow column metadata. Continue reading
SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous column. Column ID is a number that is unique to a dataflow within the package. Similarly ErrorCode contains the error number that caused the row to be rejected. At the moment, SSIS by default does not provide ErrorColumn name and ErrorCode description. This post describes a technique to get the name of ErrorColumn. Continue reading
Dataflow Discoverer (DFLD) is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting. Continue reading
This post compares Analysis Services and PowerPivot in 30 seconds. Continue reading
If you recently upgraded to SQL Server 2008 or considering an upgrade, you would be glad to know that the Deployment Wizard of Analysis Services works in the way it should.Roles retain their existing permission to cubes and mining structures after deployment. Existing permissions are not lost. Dimension Data and Cell Data permissions are preserved in the role definition after deployment. And Role membership stays put.
Continue reading
Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected. Continue reading
The greatest strength of PowerPivot is the ability to create relationship with different sources of data and piggyback on this relationship for various kinds of analysis. PowerPivot relationship requires one-to-many relationship between the parent and child tables. But what happens if the relationship between parent and child tables is many-to-many instead? For e.g. you might want to establish a relationship between a fact table that has a Business Key to the dimension table and that dimension table maintains history. In this post I explain how to establish a relationship between two tables that do not exhibit one-to-many relationship on their Business Key using a technique that is similar to an ETL Type 2 SCD lookup using Data Analysis Expressions (DAX). Continue reading
Pivoting is typically used in DW solutions to load multi-valued dimensions. In my earlier post, I demonstrated how SSIS Pivot component works and the advantages of using SSIS Pivot component over its equivalent T-SQL Pivot statement. In this post, I explain a technique to Pivot data when Pivot Column is not explicitly available. Continue reading
Pivot transformation converts multiple rows of data into a single record by using an input column that has a finite set of distinct values. Pivoting is typically used in DW solutions to handle multi-valued dimensions. In SQL Server, Pivoting is done using either a) T-SQL Pivot statement or b) SSIS Pivot Transformation .There are two significant benefits that stand-out in favour of using SSIS Pivot Transformation for ETL scenarios. The remainder of this post explains just that.
Continue reading
SSIS comes with an out-of-box SCD Wizard to handle Type 1 and Type 2 Slowly Changing Dimensions (SCD) which is a fundamental ETL requirement. However the SCD wizard component has some serious drawbacks – both from operational and functional perspectives that make it unusable for practical purposes. Several workarounds have evolved over time and in this post I would like explore the different alternative options to handle Type 1 and Type 2 SCD without using the out-of-box SCD Wizard Component. Continue reading
When it comes to Data Modelling you cannot go past the contributions of Ralph Kimball and Bill Inmon. There are numerous articles/white papers that go to great detail in dissecting their modelling techniques. There are serious fanatics on both camps. Rather than getting caught up in the debate, this post gives a snippet of both Kimball and Inmon’s model and let you choose what is right for you next BI project. Continue reading
Some time back I published a post on post-installation steps for Analysis Services. That post was written with SQL Server 2005 in mind. In SQL Server 2008 – Data Dir, Log File Dir, Temp dir and Backup dir can now be specified as a step BEFORE installation of Analysis Services. There is no need for post-installation steps in SQL Server 2008, unless you missed configuring this step during installation. Continue reading
According to Gartner, Business Intelligence (BI) is one of Top 10 strategic technologies organizations will invest in the next few years. Dashboards play a significant role in BI to deliver concise high-level decision support information to decision makers. With so many Dashboarding tools available, the choice of right tool often boils down to the users of dashboarding tools. Typically there are four user groups that use dashboarding tools… Continue reading
During installation of Analysis Services, most of the properties are configured with a default value. These properties can be reconfigured any time after installation from SQL Server Management Studio. For most properties the default values works just fine. However, there are few properties that are best reconfigured immediately after installation to prevent down-time when the system is in production. This post discusses the bare minimum properties that must be reconfigured after an Analysis Service Installation. Continue reading
After many implementations, I have a strong inclination and preference to deploy SSIS packages to File System rather than to the Server. File System method of deployment is clean and elegant. It encourages package reusability and in some instances it’s the only feasible option. Here are two common ETL scenarios to support my view. Server deployments exist for purpose of backward compatibility with the DTS packages migrated from earlier versions of SQL Server when the concept of package config did not exist. For new SSIS development, File System Deployment is the way to go.
Continue reading
A common misconception among developers is that Load, Stress and Performance Testing mean the same. Well, they are not. There are subtle yet distinctive differences between each of these methods of testing, their purpose and the audience they target. I’m not a tester, so I won’t be going into the nuts and bolts of each of these methods of testing, rather I would like to present a very high level view on this subject that developers can relate to (and hopefully avoid confusion that I found myself in…). Continue reading
In this post, one of the cool feature of Excel called “Convert to Formulas” is explained with a practical illustration of it’s use and also a word of caution… Continue reading
In this post the different methods of securing dimension data is explained and compared with an emphasis on special case scenario – the Role Playing Dimension. Continue reading
In this post, transaction logging between SQL INSERT Statement and SSIS package is compared and how SSIS miminal logging can improve performance in typical high volume data warehouse ETL scenario. Continue reading
Analysis Services security model is role based. Within a role, the cube data can be secured by either Cell data or Dimension data. In this post, I compare the relative merits of each method. Continue reading
Visual Studio Team System (VSTS) Test Edition is a valuable tool to load test web applications. This post is about using VSTS Test Edition to load test SharePoint Excel Services report. In particular, I explain the solution to handle session time-out issues encountered with Excel Services web test that could invalidate the load test. Continue reading
Analysis Service Security model is role based and relies on Windows authentication. This could pose a challenge for development and support teams to test/troubleshoot role permissions because of sensitivities around windows password. Thankfully Microsoft SQL Server Management Studio (SSMS) provides a feature to test/troubleshoot role permissions and the best thing is – you don’t need to know the password of the windows account you are testing. Continue reading
Page Viewer is an out-of-box SharePoint web part that functions as a placeholder for web pages, folders and files that reside usually outside the SharePoint environment. One such use of Page Viewer is to display a Reporting Service report which is not integrated with SharePoint. This means with a little bit on tinkering Page Viewer can be effectively converted into a Report Viewer. Continue reading
When implementing Form Based Authentication (FBA) for SharePoint sites, ensure that your role members and role names do not have commas in them. A comma in any role member attribute or in role name, throws “Error: Access Denied” exception. Continue reading
Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Service Cube in disconnected mode. The Offline OLAP feature basically snapshots a version of the cube into a local cube file which can be used by Pivot Tables. In this post I explain different scenarios where offline mode may be helpful and how to get started on offline OLAP. Continue reading
Have you encountered this error message when opening an Excel Service Report in Microsoft Office SharePoint Server (MOSS)? “You do not have permissions to open this file on Excel Services. Make sure that the file is in an Excel Services trusted location and that you have access to the file”. In this post, I provide solutions to get rid of this error message. Continue reading
Deploying Report Actions in Analysis Services is an extension to a popular post Reporting Action in Analysis Services on this blog . In this post, I explain the steps to deploy a report action using Analysis Services Deployment Wizard. By using the Analysis Service Deployment Wizard, Report Actions can be deployed effortlessly without hard coding the report URLS in your Analysis Services project. Continue reading
This blog post is for SharePoint Dashboard Designers. Often Dashboard Designers find it annoying to discover that their web part is either too big (leaving ugly scroll bars) or too small (leaving empty space) for the web zone and they are not able to modify the dimensions of the web zone. In this post, I provide a quick solution to re-size web zones. Continue reading
The use of raw files as SSIS data source is highly recommended for huge volume of data due to its superior read/write performance. Raw files store data in a very basic format that requires almost zero translation and parsing. This enables faster data reads/writes when compared to Flat Files and even faster than Database tables. The usage of raw files as SSIS data source comes with one caveat. Since the raw file format is native to the source, the raw file’s metadata must be available at design time to the package developer to build the data flow. In this post I explain a trick to workaround this caveat. Continue reading
These are my open source projects available in CodePlex.
1. SSIS Credit Card Number Validator 08 (CCNV08)
2. SSIS Credit Card Number Validator 05 (CCNV05)
Credit Card Number Validator (CCNV) is a Custom SSIS Data Flow Transformation Component that determines whether the given input is a valid credit card number or not. CCNV takes String/Numeric input and performs a Synchronous, Non-Blocking transformation using Luhn Mod 10 algorithm and validates whether the input is a valid credit card number or not. The output is true if the input is a valid credit card number and false otherwise. CCNV08 is compatible with SQL Server 2008 while CCNV05 is compatible with SQL Server 2005. Continue reading
Analysis Service Database can be backed up from SQL Server Management Studio (SSMS). This process is pretty much UI based. In this post, I explain two different techniques to automate the backup of an Analysis Service Database. Continue reading
Analysis Management Objects (AMO) allows you to programmatically interact with the object model of Analysis Services. The AMO layer interacts with Analysis Services by issuing XMLA commands. In this post I will explain the steps to automate the process of securing an Analysis Service Cube by building wrappers around AMO objects. Continue reading
Analysis Services allows cube browsers like Excel to fire events which are handled by the Actions defined in the cube. In this blog post, I will explain a specific type of cube action called Reporting Action. Reporting Action enables information consumers to fire a SQL Server Report when a specific condition is met while browsing the cube. Continue reading
Custom SSIS Components consists of Design-time methods and Run-time methods. Debugging a Design-time method is different from a Run-time method. This post explains the techniques to debug Design-time and Run-time methods using the Custom Data Flow Component, Credit Card Number Validator as an example.
Continue reading
In this post, step-by-step instruction is provided to Build and Deploy Custom SSIS Components. Continue reading
This post provides step-by-step instruction to create a Custom SSIS Data Flow Component to Validate Credit Card Numbers using Luhn Mod10 Algorithm. Continue reading
In this post I’ll explain a nifty trick that helps you clone an existing Excel PivotTable and change the data source without having to build the second PivotTable from scratch. Continue reading
This post explains the Measure Group property, IgnoreUnrelatedDimenisons and presents a compelling case to support why its default value should be FALSE. Continue reading
This post defines the basic terminology of Analysis Services Cube from an Information Consumer/Analyst perspective. An illustration is provided to picturize the Analysis Services Cube Model. Continue reading
There are five different ways of creating KPIs in Sharepoint Server 2007. This post compares the relative merits of each method and suggests scenarios where a particular method is suitable over others. Continue reading
High Speed SSIS Data Transformation by using Raw Files. This post explains a technique that transforms 20M records in less than 10 minutes. Continue reading
