Posts

Performance Considerations for Securing Parent-Child Dimensions

/0 Comments/in /by

Parent-child dimensions are a very useful feature of Analysis Services to model recursive hierarchies, such as an organizational hierarchy. Recently, I had to apply dimension data security to a parent-child dimension. The allowed set expression used the factless fact table approach (see my Protect UDM with Dimension Data Security article for more details), as follows (translated to the Adventure Works schema):

IIF

— check if the user has full access

(StrToMember(“[User].[Login].&[” + Username +”]”).Properties(“FullDimensionAccess”,TYPED)=True , [Employee].[Employees].[All],

Generate

(

Exists ([Employee].[ Employee].[ Employee].Members, StrToMember(“[User].[Login].&[” + Username + “]”), “Bridge Employee Security”)

, {LinkMember (Employee.Employee.CurrentMember, Employee.Employees)}

)

)

To avoid loading the factless fact table with all dimension members for users with full dimension access, the expression checks if the user is flagged as such. Since Analysis Services doesn’t provide a way to ignore dimension data security from the allowed/denied set, the expression returns the hierarchy top member [Employee].[Employees].[All] and this is where the issue is. Returning the All member causes the server to scan recursively the entire hierarchy and perform AutoExists against all other attributes within the P-C dimensions. Unlike regular dimensions where the server can infer attribute relationships quickly, a P-C hierarchy requires dynamically checking each member. This can slow the user connect time significantly. In my case, the P-C dimension had about 300,000 members and the connect time for users with full dimension access exceeded two minutes. Needless to say, users were not happy.

There are several workarounds (none of them ideal):

  1. Move users with full dimension access to a separate role to avoid the IIF check. This wasn’t an option in our case.
  2. Remove as many attributes as possible from the P-C dimension to reduce the number of attributes for the AutoExists check.
  3. Add a new hidden P-C dimension that has the dimension key and P-C attribute. Apply dimension data security on this dimension and leave the original P-C dimension intact.
  4. Refactor the P-C dimension and move its leaf members to a new dimension in order to reduce the number of members in the P-C dimension.

I logged a new wish on connect.microsoft.com and requested the ability to ignore dimension data security. Please vote so we can have a more elegant way to address such requirements without refactoring the cube design.

Dimension Data Security and Cube Script

/0 Comments/in /by

If you have worked with Analysis Services dimension data security for a while, you know that MDX allowed/denied set expressions can become rather complex. You might be tempted to externalize these expressions in the cube script for brevity or reusability reasons. For example, you might decide to add a calculated member that returns a list of allowed partners that the user is authorized to see over a factless fact table as follows:

CREATE MEMBER CURRENTCUBE.[Measures].[PartnerSecurityFilter] AS “Exists([Partner].[Partner].[Partner].MEMBERS, StrToMember(“”[User].[Login].&[” + Username + “]””), ‘Bridge Partner Security’; 

Then, in the allowed set you might try reusing the member definition in the allowed set expression, such as:

StrToSet([Measures].[PartnerSecurityFilter])

This sounds like a great idea but it doesn’t work. Specifically, you will find that it works for the first member of the SSAS role that connects to the server but it doesn’t work for any other members of the same role who connect to the server. To make things worst, the other users will inherit the same security access as the first user and this opens also security vulnerability.

While I am not excluding the possibility for a bug, I think what’s going here is related to the fact that the MDX script is evaluated after dimension data security is applied. What’s weird is that even dynamic expressions, such as StrToMember or StrToSet don’t work. I wasn’t able to find a workaround besides having the entire logic in the allowed/denied set expression and not referencing the cube script at all. If dimension data security becomes complicated, consider externalizing it to an Analysis Services stored procedure.

Finding Kerberos in Forests

/0 Comments/in /by

I’ve made a decision. My next book will be Applied Kerberos BI. Jokes aside, I’ve been doing a lot of security work lately to get Kerberos working for internal and external users. Here is my latest success story.

043011_0143_FindingKerb1

In this case, the customer has two AD forests. The internal users are on a DOMAINA in FORESTA. The production servers are in DOMAINB. The external user accounts are in DOMAINC. Both DOMAINB and DOMAINC are in a separate forest. There is a bi-directional trust between DOMAINA and DOMAINB and between DOMAINB and DOMAINC. DOMAINC trusts DOMAINA.

After registering SPNs, etc. we’ve found that Kerberos doesn’t work. Or, to be more specific, it works partially. If a service account from DOMAINB is used to run SSRS and SSAS, the double hop (user-SSRS-SSAS) is successful for users on DOMAINC but not for DOMAINA users. And vice versa, if a DOMAINA service account is used, only DOMAINA users can run reports. Upon a fair amount of tracing, we saw that when DOMAINB tried to resolve the SSAS SPN it fails with KDC_ERR_S_PRINCIPAL_UNKNOWN error. For some obscure reason, it was trying to resolve the SPN by using the SSAS NetBIOS name against the DOMAINA domain controller and DOMAINA was failing to find it. You would expect Kerberos to fall back and do a second search in the domain the end user belong to but it doesn’t. Perhaps, it gets lost in the forests.

How do we solve this horrible issue? Use a FQDN SSAS server name in the report data source. For example, instead of SSASServer, we would use SSASServer.DOMAINB.LOCAL. This forces the Kerberos to look up the SSAS SPN in DOMAINB. Please note that in this case, the SSRS server runs in a native mode. I don’t know what will happen if the SSRS server is configured for SharePoint integration. The Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products whitepaper states “Basic Kerberos delegation can cross domain boundaries in a single forest, but cannot cross a forest boundary regardless of trust relationship. Kerberos constrained delegation cannot cross domain or forest boundaries in any scenario.” This makes me somewhat skeptical but it might apply to the scenario where SharePoint and SSAS are in two different forests.

Analysis Services Refusing to Start

/0 Comments/in /by

I had a laptop which blue screened and shut down. Some files got corrupted and fixed by the CheckDisk. Apparently, something got whacked during the process because the Analysis Services would refuse to start with the following error:

The service cannot be started: XML parsing failed at line 1, column 40961: Illegal xml character.

Unfortunately, Analysis Services doesn’t support starting in recovery mode, such as SQL Server Database Engine. Usually, when you get a message like this, the error message tells you which object file, such as dimension, got corrupted. To fix, you would delete the database from C:\Program Files\Microsoft SQL Server\<SSAS Version>\OLAP\Data and reprocess the database or restore from backup. But in this case, the error message in the Windows Event Viewer didn’t indicate any object. As it turned out the issue was a corrupted master.mvp file in the Data folder. The resolution path was:

  1. Copy the master.mvp file from another SSAS instance.
  2. Upon using SSMS to connect, you would probably see that SSAS wouldn’t recognize you as administrator. For example, going to the instance properties errors out with:
    The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties. (Microsoft.AnalysisServices.ManagementDialogs).
    To fix this, right-click on the SSMS icon and click Run as Administrator. Then, go to the SSAS server properties (you should let you without error) and add the Administrators group or your account.
  3. Reprocess all databases or restore them from backup.

Data Driven Subscriptions and Row-level Security

/2 Comments/in /by

Scenario: A customer wants to have report row-level security and data-driven subscriptions. If you have used SSRS for a while, you will now the cornerstone of report row-level security is User!UserID which returns the user identity. But here is the catch – SSRS will refuse creating a data driven subscription if a report has an expression with User!UserID.

Solution: How do we solve this horrible issue? A great hack is in order. We will disguise the User!UserID in an embedded function inside the report or in an external assembly so SSRS can’t see it. (The reason why this function is named GetHashedUser although no hashing is in place will become obvious in a moment)

Public Function GetHashedUser() as String

return Report.User!UserID.ToLower()

End Function

 

Believe it or not, this lets the data-driven subscription going. But there is another catch. A data-driven subscription runs in unattended mode and you need to pass the user identity as a parameter. So, when the report is executed live we want to pass the internal User!UserID to the report query or stored procedure. But when the report is subscribed to, we want to pass the user identity as a parameter. A simple expression for the query parameter will do the trick.

=iif(Parameters!UserID.Value =“”, Code.GetHashedUser(), Parameters!UserID.Value)

If the parameter is empty (live delivery), we will call the GetHashedUser function, otherwise, we will pass the parameter value to the database. So far so good, but we now have a security hole. There is nothing stopping the end user from spoofing the report parameter and passing another user’s identity to it to force the report take the data-driven subscription path. To fix this, we will need some level of encryption. I suggest a solution that encrypts the user identity with SHA1 hash both in the report and the database. This should keep spoofy users at bay.

You can download the complete code with an AdventureWorks sample report here (requires registration).

Note: The above solution assumes a report connection string with stored credentials which is what you would typically have for T-SQL (relational) reports. What about SSAS with Windows and dimension security? Not a good story and no changes in Denali unless you vote for the following suggestions on connect:

  • Enabling Subscriptions for non-stored credentials reports only has 3 votes on Connect (see link)
  • Fixing the SSAS connection string issue has only 4 votes on Connect (see link)
  • Enabling Shared Data Sources to have Expression Based Connection strings has only 4 votes on Connect (see link)
  • Can’t create a subscription if data source is set to impersonate the authenticated user (see link)

 

SSAS and data-driven subscriptions has been the bane of my existence. Greg Galloway has an excellent writeup here. In a nutshell, clumsy workarounds include duplicating the report definitions and using an embedded data source with expression-based connection strings, or implementing the row-level security in the report (as you would do with relational reports). So, please vote! Let’s see if can convince Microsoft that we do use SSAS and this is a wide-spread issue that deserves to be fixed ASAP.

Dundas Analytical Dashboard and Analysis Services

/0 Comments/in /by

In my Dundas Dashboard and MS PerformancePoint Comparison article, I introduced you to Dundas Dashboard and explained how you can use it to rapidly assemble compelling performance dashboards. With Dundas Dashboard, you can also build analytical dashboards to let users create interactive, web-based reports from OLAP databases, such as Microsoft Analysis Services, as I will show you in this article.

Drillthroughs Going Wild

/0 Comments/in /by

Got bit by an unfortunate bug which has been around for four years since it got reported on connect.microsoft.com with Analysis Services OLAP drillthrough. It caused me almost a day to figure out. Drillthrough actions (both default and explicit drillthrough) won’t return any rows on some cells or would return repeating rows for other cells. As I posted on connect the bug springs to action when:

  1. The measure group has IgnoreUnrelatedDimensions=False. Bryan Smith provides a great coverage of IgnoreUnrelatedDimensions in this blog.
  2. A dimension attribute has IsAggregatable set to False. Setting the dimension default member either using the DefaultMember attribute property or via script doesn’t make a difference.

In my case, I had a Time Calculations dimension which was a free floater and it had IsAggregatable=False. Excluding the unrelated dimensions from the drillthrough action doesn’t make a difference either. The only workaround I’ve found is to set IgnoreUnrelatedDimensions back to True. Vote for a due fix, please!

Unaggregatable M2M Dimensions

/0 Comments/in /by

I was called upon to troubleshoot an issue with creating an aggregation design for an existing cube. The Design Aggregation Wizard would refuse to design aggregations with the following cryptic message:

Errors in the aggregation designer. The ‘Perspective’ dimension has no attribute for the aggregation design. The server cannot design aggregations when this attribute is missing.

Upon a closer look, it turned out the Perspective dimension joins the measure group via a many-to-many relationship. More importantly, its IsAggregatable property was set to False to suppress the All member because it would be meaningless to aggregate data across the members in this particular dimension. Instead, the cube script would set the dimension default member with an ALTER statement.

How do we solve this horrible problem? Here is my solution:

  1. Set IsAggregatable property of the dimension attribute back to True.
  2. Add a scope assignment to the cube script to nuke the All member. To be more specific, here we set the All member to null to take advantage of the fact that most browsers suppress empty cell and the All member won’t show up.

    Root ([Perspective]) = null;

  3. Design aggregations as usual.

Notice that I don’t remove the ALTER statement to assign a default member. If there is no default member, you would see that slicing by any other dimension other than Perspective would miraculously return no data, just like it would if no data exists in the cube. You must set up a default member if you remove or set the All member to null.

Report Actions for Report Server in SharePoint Mode

/0 Comments/in /by

Report actions are an Analysis Services extensibility mechanism that lets end users run a Reporting Services report as they browse the cube. A customer reported that they have trouble setting up a Reporting Services action for a report server configured in a SharePoint integrated mode – a scenario which appears that wasn’t tested properly by Microsoft.

First, the customer wanted to pass multiple parameters to the report. This was achieved by setting up the report action Target Type to Cells and Target Object to All Cells. This setup allows the user to right-click any cell on the cube to initiate the action. What’s more important is that an action-capable browser, such an Excel, will be able to collect the coordinates of all dimensions used in the browser, such as those added to the filter area, so you can pass them as parameters to the report.

121210_0238_ReportActio1

A condition is further specified to restrict the action. Because the cube browser will be passing three parameters to the report, the condition enables the action only if the corresponding members’ current member is not the All member.

This is where things get interesting. You need to specify the server name and report path. Instead of pointing to the report server, you should point to the SharePoint library using the syntax that Prash Shirolkar from the Reporting Services explains in his blog. I haven’t tested this but I’d expect that if you reference the report server, the report will use the report definition copy on the report server and not the one stored in SharePoint. Recall that the latter is the master copy of the report definition and the one in the report server might be outdated. However, when you reference the SharePoint library, you’ll find that Analysis Services doesn’t prefix the parameters with rp:, which in turn makes the report server ignores the parameters. The ultimate hack is to prefix the parameters yourself:

121210_0238_ReportActio2

Here, the server name is set to the SharePoint site URL. I’ve found that actually it doesn’t matter where you break the URL to server name and report path. SSAS will concatenate them, so I could have set the server name to elite only and moved the rest of the URL to the Report Path line. Also, notice that you don’t use http: as in http://elite/sites/reports. SSAS will add the protocol for you.

VertiPaq Column Store

/0 Comments/in /by

In SQL 11, the VertiPaq column store that will power the new Business intelligence Semantic Model (BISM) will be delivered in three ways:

1. PowerPivot – in-process DLL with Excel 2010.

2. A second storage mode of Analysis Services that you will get by installing SSAS in VertiPaq mode.

3. A new column stored index in the SQL RDBMS.

The third option picked up my interest. I wanted to know if a custom application will be able to take advantage of these indexes outside VertiPaq. For example, this could be useful for standard reports that query directly the database. It turns out that this will be possible. The following paper discusses columnstore in more details. In a nutshell, SQL 11 will run the VertiPaq engine in-process. Here are some highlights of the VertiPaq columnstore:

  • You create an index on a table.
  • You cannot update the table after the index is created. Not a big limitation since a data warehouse is typically loaded on a schedule so the index can be dropped and rebuilt at the end of the ETL process.
  • The relational engine scans the entire table and loads it into memory in a Vertipaq store. As you could imagine, the more RAM the better.
  • Query performance will be great but probably slower than VertiPaq in SSAS.
  • Creation of this index isn’t cheap, but the query performance gains should justify using it in common DW scenarios.
  • From performance standpoint, it will be best to load data in VertiPaq (SSAS store) instead of using column store.