Protect UDM with Dimension Data Security Reloaded

SQL Server Magazine published the second part of my Protect UDM with Dimension Data Security article in the October issue of the magazine. In this article, I explain how to use MDX expressions to implement dynamic dimension security. I discuss two practical approaches for meeting advanced requirements for securing UDM data. First, I present a solution in which the security policies are stored in a factless fact table. Next, I explain how to leverage SSAS stored procedures to integrate UDM with an external security service.

The article is available for public access. Enjoy!

Scale-Out Querying with Analysis Services Using SAN Snapshots

If your organization can afford SAN, here is another best practices article by Carl Rabeler from the SSAS team. The Scale-Out Querying with Analysis Services Using SAN Snapshots article “describes the use of virtual copy SAN snapshots in a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services to increase the number of concurrent queries to Analysis Services servers with a rapid response time per query.”

MCTS Self-Paced Training Kit (Exam 70-445): Microsoft SQL Server 2005 Business Intelligence—Implementation and Maintenance

Those of you preparing for Microsoft exam 70-445 Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance may be interested to know that Microsoft Press has just published a training kit with the same title which I co-authored with Erik Veerman, Dejan Sarka, and Javier Loria from Solid Quality Learning. I wrote four of the SSAS chapters. I also took the actual exam prior to writing the content so I hope the book will help you to prepare and pass 70-445.

SSRS 2008 Variables

As I mentioned in my Under the Hood post, the SSRS 2008 engine has been redesigned to perform on-demand report processing for better scalability. As a result, textbox values are calculated on-demand every time the containing page is rendered. Irrespective of the on-demand behavior, values of dataset-bound textboxes won’t change because the underlying dataset is cached in the report database. Therefore, the end result for dataset-bound textboxes is the same as with SSRS 2005.

What about expression-based values? For example, you may have a textbox that references some custom method. Unlike SSRS 2005, the new engine will execute that method each time the page is rendered. This may be or may not be what you want. If you need up-to-date information, the new behavior will be welcome. However, there will be cases, when you may need the old behavior where the custom code is executed once. For example, imagine that the custom code takes very long time to execute and you want to cache the result. Or, you don’t want the report results to change when you navigate pages, such as when you need to deal with currency conversion rates. Enter variables – a new feature in SSRS 2008.

In SSRS 2008, variables provide a mechanism to guarantee an at most once evaluation semantics. Just like programming variables, SSRS variables store values. Variables can be scoped at report or group levels (note that the CTP 4 build of the Report Designer Preview exposes only report variables). A report-level variable is evaluated once for the lifetime of the report. A group-level variable is evaluated each time the group changes.

Report variables

They are accessible from within the report body and page sections.

<Report …>

<Variables>

<Variable Name=”VExchangeRate”>

<Value>

=MyCustomAssembly.GetExchangeRate()

</Value>

</Variable>

</Variables>

Group variables

Their value is only defined within the current group instance and the inner group hierarchy.

<TablixMember>

<Group Name=”table1_Group1″>

<GroupExpressions>

<GroupExpression>=Fields!SupplierID.Value</GroupExpression>

</GroupExpressions>

<Variables>

<Variable Name=”VProductCountPerSupplier”>

<Value>

=Count(Fields!ProductID.Value)

</Value>

</Variable>

</Variables>

</Group>

Here is a report that demonstrates a group variable.

090907_1419_SSRS2008Var1

The Direct Code column has an expression-based textbox that references the custom method directly =Code.Util.GetValue(). The GetValue method simply generates a random value. When I navigate from and back to a page, the Direct Code column values change as a result of the on-demand behavior. In contrast, the Var column uses the following expression: =Variables!Var2.Value. The Var2 variable is declared as a group-level variable inside the tablix group:

<TablixMember>

<Group Name=”Tablix1_ProductCategory”>

<GroupExpressions>

<GroupExpression>=Fields!ProductCategory.Value</GroupExpression>

</GroupExpressions>

<Variables>

<Variable Name=”Var2″>

<Value>

=Code.Util.GetValue()

</Value>

</Variable>

</Variables>

</Group>

Now, the result is completely different. Because a variable stores the result, the column values don’t change within the same group. More importantly, the values are constant between page refreshes. At this point, you are probably thinking about other useful scenarios for variables. However, you cannot use variables to maintain state.  They are evaluated at most once and are read-only.