SQLAuthority News – Download Whitepaper – Enabling and Securing Data Entry with Analysis Services Writeback

SQL Server Analysis Service have many features which are commonly requested and many already exists in the system. Security Data Entry is very important feature and SSAS supports writeback feature.  Analysis Services is a tool for aggregating information and providing business users with the ability to analyze and support decision making in their business. By using the built-in writeback feature in Analysis Services, business users can also modify their data points to perform what-if analysis or supplement any existing data. The techniques described in this article derive from the author’s professional experience in the design and development of complex financial analysis applications used by various business groups in a large multinational company. The whitepaper shows how to enable and secure data entry with Analysis Services writeback.

Download Whitepaper Enabling and Securing Data Entry with Analysis Services Writeback.

What to Check Before You Turn On Analysis Services Writeback

Writeback sounds simple, but it changes how you think about a cube. Normally users only read from it. Once you let them type numbers into it, you are running a small data entry system, and it needs the same care as any other one.

Before you switch the feature on, I would check these points:

• Permissions: only the roles that really need to enter data should get read/write access to the cube. Everyone else should stay read-only.
• Where changes land: the values users enter are stored as change records in a writeback table in the relational database. Back up that table and decide who owns it.
• Allocation: when someone changes a total, the new value has to be spread down to the detail cells. Entering numbers at a very high level can touch a huge number of cells and feel slow.
• Review: decide who approves the entered numbers and when they become part of the official data.

Keep in mind that writeback belongs to multidimensional cubes. If your model is tabular, you will need a different approach for data entry. For a first try, pick a small budget or forecast cube with a few users. Excel PivotTables have a What-If Analysis option that sends changes back to the cube, so your users can test it in a tool they already know before you roll it out to more people.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.