Minimal Post-Installation Steps for Analysis Services

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 in production environment.

SSIS Package Deployment Method – File System or Server ?

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:

Load, Stress and Performance Testing from a Developers Perspective

A common misconception 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…).

Convert to Formulas

Excel is the first choice browser to create reports from an Analysis Service cube. The cube data is displayed as a pivot table format which is extremely convenient and flexible format to slice and dice information. Pivot tables, however can only display data that exist in the data source. There may be instances where you might need additional calculations to be displayed in your report that could be derived from existing cube measures. For e.g. having a different method of determining sales quota for a particular region, calculation of Averages and Variances, what-if analysis and so on. In these circumstances, the Convert to Formula feature of Excel comes really handy.

Securing Role Playing Dimensions in Analysis Services

In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube

Row Insert from SSIS package Vs Transact-SQL Statements

SQL Server writes transaction logs sequentially. The size of transaction logs when using sql INSERT statement depends on the Recovery Model of the database and the type Transact-SQL statements used. Database recovery model can be – Simple (min), Bulk-Logged (intermediate) or Full (max). While this is true for Transact-SQL INSERT statements, records inserted through an SSIS Data Flow Destination (like SQL Server Destination) exhibit a different logging behaviour. This is because the INSERT statements issues from Integration Services engine is always minimally logged and it bypasses the recovery model of the database engine. This results in significant performance improvement by reducing the sequential disk writes, especially when dealing with huge recordsets which is typical in a data warehouse ETL scenario. That’s another reason why SSIS packages perform better over Transact-SQL Stored Procedures for ETL purposes.

Cell Data Access Vs Dimension Data Access in Analysis Services

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.
 

Load Testing Excel Services Report – Deriving Query String Parameters sr and wb

 

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.

Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio

  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.   

Using Page Viewer Web Part to Display Reporting Services Reports in SharePoint

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. 

High Speed Data Transformation Using Raw Files

This post explains a SSIS technique that transforms 20M records in less than 10 minutes by using Raw Files.

Pages: 1 2 3

Comparison of KPI Types in SharePoint Server 2007

SharePoint Server 2007 offers five different methods to create Key Performance Indicators (KPIs) 

1. Using data from published Microsoft Excel 2007 spreadsheet.
2. Using KPIs defined in SQL Analysis Service Cube.
3. Using dashboard created in PerformancePoint 2007.
4. Using data from SharePoint list.
5. Using manual data entry.

This post compares the relative merits of methods 1-3 and suggests scenarios where a particular method is suitable over others.

Analysis Services Cube Concepts – Made Simple

This post defines the basic terminology of Analysis Services Cube from an Information Consumer/Analyst perspective.

IgnoreUnrelatedDimensions

IgnoreUnrelatedDimensions is a property of Measure Groups. By default its value is TRUE. So what does that mean?

How to Clone an Excel PivotTable?

Ever came across a situation where you have to compare PivotTable data from same data source but from different environments, for e.g. to compare data between Test and Production environment?  In this post I’ll explain a nifty trick that helps to clone an existing Excel PivotTable and change its data source without having to create the clone PivotTable from scratch.

Custom SSIS Data Flow Component – Credit Card Number Validator

 Custom Data Flow Component – Credit Card Number Validator

 

SQL Services Integration Services (SSIS) provides a wide range of out-of-box components to perform almost any ETL task. In addition, the object model of Integration Services allows you to create re-usable custom components.  Different types of custom objects can be developed, including:

• Custom tasks.
• Custom connection managers.   Connect to external data sources that are not currently supported.
• Custom log providers.    Log package events in formats that are not currently supported.
• Custom enumerators.   Support iteration over a set of objects or values formats that are not currently supported.
• Custom Data Flow Components.   Can be configured as sources, transformations, or destinations.

In this post, step-by-step instruction is provided to create a Custom SSIS Data Flow Component called Credit Card Number Validator. This component validates Credit Card Numbers using Luhn Mod 10 Algorithm.

Pages: 1 2 3 4 5

Reporting Action in Analysis Services

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.

AMO Wrappers for Securing Analysis Service Cube

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.

Pages: 1 2 3 4

How to Automate Analysis Services Database Backup?

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.

CodePlex Projects

 Projects

I’m the coordinator on these CodePlex projects.

• SSIS Credit Card Number Validator 05 (CCNV05)

How to Create Raw File for Use as SSIS Data Source?

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.

Re-sizing Web Zone in SharePoint Dashboards

A SharePoint Dashboard is a web page made up of web parts. Web Parts exist within containers called Web Zone. In SharePoint, while the properties of the web part are exposed to the Dashboard Designer, the web zone is virtually untouchable. 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.

Deploying Report Actions in Analysis Services

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

Getting rid of error message “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 Microsoft Office SharePoint Server (MOSS)

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

Offline OLAP – Working with Disconnected Analysis Service Cube

 

Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Services 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.

Avoid Commas in FBA Roles and Members

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.