Permissions Issues in Dynamics CRM 2011 when Users Inherit Roles from Teams

Setting up a CRM demo I noticed what appears to be a problem in the CRM 2011 security model which affects users whom only inherit a Security Role from their membership in a Team, and have no roles of their own.

My user is in a Team, and the Team has the out-of-the-box Customer Service Representative role. But my user can’t create a Service Activity like he should be able to:

I thought the team permissions were right for creating Activities, but MSDN just has a ‘coming soon page’ for both the roles and doesn’t even list the Activity entity so I wasn’t getting much help there.

Some error dialogs in CRM have a little ‘view log’ button, which helps, but this one didn’t. However the TechNet article ‘Troubleshooting Microsoft Dynamics CRM’ was quite helpful here (written for CRM 4.0 but much is still relevant), describing how you activate the detailed error dialog by modifying this setting in the app.config:

<add key="DevErrors" value="On"/>

This gave me something a bit more concrete to go on:

SecLib::AccessCheckEx failed. Returned hr = -2147187962, ObjectID: 00000000-0000-0000-0000-000000000000, OwnerId: {blah}, OwnerIdType: 8 and CallingUser: {blah}. ObjectTypeCode: 2500, objectBusinessUnitId: {blah}, AccessRights: CreateAccess

Ok, so I can’t create something. But interestingly a bit of poking about in the metadata schema in the database (Metadata.Entity) reveals ObjectTypeCode 2500 is not actually Activity, as I suspected, but UserEntityUISettings.

Hmm. The team’s got permissions for that too I thought:

... but that permission can only be applied at the user level. So I wondered if it wasn’t transitive over the Team correctly, and added the user directly to the role.

And then it all worked.

Perhaps there is an explanation somewhere, but I’m damned if I can find it. Services and Service Activities are new in 2011 and seem to be skipped entirely by the two CRM books I looked at. There is some documentation around the permissions model, but it’s incomplete, and most blog posts I’ve read seem to suggest diagnosing issues is a bit of a crapshoot, which is pretty piss poor for a business-orientated application if you ask me. And I’d hate to have had to diagnose this in the cloud, without access to the database...

More whinging about Hash Aggregate

I previously posted on why Hash Aggregate is to be avoided if possible. Unfortunately Hash Aggregate vs. Stream Aggregate appears to be an ‘all or nothing’ proposal. And that sucks.

A Stream Aggregate can be relatively cheap (in terms of memory pressure) because it can leverage the ordering (typically clustered index ordering) of the input data and aggregate on-the-fly. By contrast the Hash aggregate has to cope with completely disordered data streams, so must buffer all the output aggregates in memory until the input data has been entirely read:

"The other aggregation operator, hash aggregate, is similar to hash join.  It does not require (or preserve) sort order, requires memory, and is blocking (i.e., it does not produce any results until it has consumed its entire input).  Hash aggregate excels at efficiently aggregating very large data sets.”
[Craig Freedman - http://blogs.msdn.com/b/craigfr/archive/2006/09/20/hash-aggregate.aspx ]

This is a good strategy because:

• It copes with totally unordered data
• Provided you are aggregating to a significantly higher grain than the source data, the volume of aggregates in the hash (and hence memory consumption) can be relatively small.

In my case, of course, the latter isn’t true: I’m aggregating across a significant amount of data, but not aggregating very much, so I am experiencing vast quantities of tempdb spill.

My input data is partially aligned to the aggregation (the most significant column is sorted correctly), so you’d expect that the aggregation would be able to leverage that smartly, to output aggregates when they were clearly ‘done’. So, for example, if you had a clustered index on Month/Day/Category, and you were doing sum(something) GROUP BY Month, Category, you and I both know that after you’ve finished spooling data for any given month, you can output all those buckets, rather than hang on to them waiting for more. Unfortunately this doesn’t happen, which means given enough data, something will spill to disk:

“if we run out of memory, we must begin spilling rows to tempdb.  We spill one or more buckets or partitions including any partially aggregated results along with any additional new rows that hash to the spilled buckets or partitions”
[Craig’s blog again, same link as before]

One can only hope it’s the older buckets that get spilt first, but even so, when this happens you are taking the IO hit of streaming all that aggregate data to disk and then subsequently loading it all back in again. Ouch. Or you force the stream aggregate (using a hint) and take the pain of the sort. More ouch.

For many workloads this probably isn’t much of an issue, but for my data warehouse this is starting to look like a major, major bottleneck.

Setting up CRM 2011? Set the Calender format *first*

When you create a new Dynamics CRM 2011 organizational instance, the localization settings for that Organization default to US format, totally ignoring the settings of the installing user, defaults for the server etc…

You can change this in Settings \ Administration \ System Settings \ Format, but make sure you do so before you add any users to the CRM instance, because when you change it it doesn’t apply retrospectively to users already created. Hardly intuitive, I think you’ll agree, and you’d at very least expect a note (or warning) on the dialog that all you are changing (apparently) is the new user default.

eg: After changing to Australian format, new users correctly see this:

However, a user created before the change still sees this:

These users must each and every one go into their dashboard options and change their personal format settings to get what is now the system setting.

You’d notice this fairly early on I imagine, but you could still have created a bunch of content and a bunch of users, so this could be a real administrative pain in the arse.

Silverlight Spy

This looks cool – a Hawkeye for Silverlight apps (in and out of browser). Basic version is free.

Good Overview, there’s also a video on C9

NineMSN vs IE9

NineMSN still shows up as ‘compatibility view’ in both IE 8 and 9, despite what the banner ad below might suggest:

Note: I wrote this two months ago and forgot to publish it. But it’s still true...

Excel Multiselect Issues with SSAS Calculated Member

Ok, it’s an old one, but I struggled to find the right answer, so I thought it was worth a post. I knew it was an issue, and I thought I knew the answer. I was wrong.

Say you create a calculated member to return some kind of item count against a dimension, a bit like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
count( descendants(
  [Date].[Year-Month-Date].CurrentMember,
  [Date].[Year-Month-Date].[Date]
))

and it works just fine when you test it

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM [My Cube]
WHERE {
    [Date].[Year-Month-Date].[Year].[2011]
}

…and then you find it falls apart when someone queries the cube in Excel and uses multi-select in the pivot table filter. So you follow Mosha’s advice to fix it (using Existing):

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as
count( existing [Date].[Year-Month-Date].[Date] )

…but then someone points out that that doesn’t work either. More recent versions of Excel (2007, 2010) implement multi-select via sub-queries; with just a few days selected it generates a query that (simplified) looks a bit like this:

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM (
    SELECT (
        {[Date].[Year-Month-Date].[Date].&[20110101]
        ,[Date].[Year-Month-Date].[Date].&[20110102]
        })
    ON COLUMNS  FROM [My Cube]
)


and executing that doesn’t give you the right answer (it just counts the number of members in the dimension):

So the old advice, still commonly quoted, is actually wrong. The solution (as of SSAS 2008 and onwards) is to use a dynamic named set:

CREATE DYNAMIC SET [Selected Days] as (
    [Date].[Year-Month-Date].[Date]
)
CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
    count([Selected Days])
)

I found this a bit bizarre at first, but some of the posts I read were themselves a bit confused and left in the ‘existing’. This is not required, and distracts from what’s really going on here: this works by design as this is one of the problems that dynamic sets are intended to fix. A (more recent) Mosha post spells it out:

“Dynamic sets are not calculated once. They are calculated before each query, and, very important, in the context of that's query WHERE clause and subselects”

Ah. The answer, and the explanation. Finally.

When Tabs go Bad

Maybe it’s just me, but I don’t think a tabbed UI is cutting it any more for the New User dialog in Active Directory:

Also just me, but ‘Remote Desktop Services Profile’ strikes me as about twice as long as it should be for a tab header.

Update: Later I found the Active Directory Administrative Center, which is an apparently WPF-based replacement for all this, and looks much nicer. So I guess the dialog above is deprecated anyway.