What immediately struck me about the conference is that it uses the phrase “Business Analytics”, instead of “Business Intelligence”. “Business Intelligence” is used almost universally these days as a blanket phrase for all sorts of things. So I did some research on the difference between the two terms; Intelligence is a synonym for “Aptitude”, “Clever”, and “Brain Power”, all of which are fairly generic terms which could really mean whatever you want them to mean. “Analytics”, on the other hand, is a synonym for “Investigation”, “Scrutiny” and “Breakdown” which I believe to be much more of a descriptive term for the work we as BI professionals are engaged in.

Before you think I’ve set off on a self-indulgent grammar excursion, this is a really important distinction, because what we as BI professionals do, or what we should do, is provide business users with a platform for information discovery - Investigation, Scrutiny and Breakdown. It’s really important to understand that the sole reason we exist as IT professionals is not just to make lots of money and drink beer, but to support the business. At the end of the day, they’re the reason why we’re here, and if we forget that, they’ll forget about us. So with this in mind, my session, Self-Service Business Analytics in 2013, is about how we as IT professionals can assist the business using the Microsoft BI platform.

Including the term “Self-Service” in my title was always going to open me up to criticism from my colleagues. Take this blog post for example, Self-Service Business Intelligence: It’s Wrong, Bad and Shouldn’t be Anyone’s Goal – Wow, that’s a broad sweeping statement! Read the post, however, and the core point is that the business cannot (effectively) perform BI without IT professionals, but the reverse is also true; IT professionals cannot perform BI without the business. This is something that Microsoft has long recognised; effective BI systems are those that combine the traditional strengths of Corporate BI (Data Quality, Security, Governance and Performance) with the flexibility and agility of Self-Service BI. Microsoft’s term for this is Managed Self-Service BI.

My session will explore the awesome benefits of Managed Self-Service BI – how we, as IT professionals, can work with the business to achieve truly meaningful business outcomes. It is, after all, the whole reason for our existence.

If you’re a BI professional or a business user with a keen interest in analytics, this is the conference to attend in 2013. I’m really pumped about this one, and I’d love to see you there - I might even shout you an (Australian) beer!

As much as I love Policy-based Management, the documentation and error messages leave a little to be desired, so it wasn't immediately obvious how to use this property to achieve the desired result.

Here's the solution...

The trick is using the Array function as shown below. "Field" is simply @WindowsUsersAndGroupsInSysadminRole, but "Value" uses the Array function to convert a CSV list of domain accounts (AD groups or users) into an array data type for comparison with the array data type returned by the @WindowsUsersAndGroupsInSysadminRole function.

On my StrataDB development environment, the results of this policy execution is shown below;

The policy failed because the actual list (array) of sysadmin accounts includes more than the expected list (array).

Hope this helps.

Cheers,
Rod.

I’ve browsed the AdventureWorks cube, and sliced Internet Sales by the Employee Department hierarchy. The same dollar amount ($29,358,677.22) is showing for every employee, which is obviously wrong and the sort of thing that confuses everyone who uses the cube.

The issue here is that the Employee Department hierarchy is not related to the Internet Sales Amount measure, and by default, the measure’s ALL member (grand total) is shown for any unrelated dimensions.

As a developer, it’s easy to dismiss this as a non-issue; it doesn’t make any sense to slice internet sales by staff member, however, this is the sort of thing that makes business people nervous about the quality of the BI system, and whether or not they trust it.

There’s two ways around this type of problem; Perspectives and IgnoreUnrelatedDimensions.

Perspectives, an Enterprise Edition feature, allows grouping together measures and their related dimensions. In the above example, there’s a “Direct Sales” perspective which excludes the Employee dimension and includes the Internet Sales Amount measure, therefore avoiding this type of invalid cube usage.

The other option is IgnoreUnrelatedDimensions, a property of a measure group, as shown here;

The default value for this is True, leading to the behaviour in the first image. By setting this to False, as shown here, unrelated dimension slicing is prevented; if attempted, the measure value will be blank, and no dimension members will show.

More details on this property can be found in this blog post from Hilmar Buchta.

Cheers,
Rod.

The concept behind SSAS dynamic security is quite simple. Like SQL Server itself, SSAS uses role based security as a means to restrict the user’s access to various parts of the cube. A commonly cited example is restricting sales people from a particular sales region dimensioning by other sales regions e.g.; Australian sales people should not be able to view sales figures for Canada.

Using an SSAS role, we can implement this quite easily. As shown below, I’ve created a new role, and on the Dimension Data page, restricted the Sales Territory Country to Australia. On the Membership page (not shown), I would then select the appropriate Windows login(s), and those users would then be restricted from dimensioning by countries other than Australia.

So far so good. Nice and easy. But, let’s imagine a situation in which there were thousands of sales territories. Creating a role for each one of them (and then managing membership over time) is simply not feasible.

Enter dynamic security. In essence, we use a many to many structure which maintains the mappings between users and allowed dimension members in tables. Using the AdventureWorks sample, we would create a table structure like this ...

The UserTerritoryBridge table simply maps users (Windows accounts) to Sales Territories. In a typical BI implementation, this data would be maintained by an ETL process from the source system that defines such mappings. Note that the structure allows for 1 user to be mapped to many territories and vice versa. A true "many to many" relationship.

The next step is defining the usage of these tables inside Analysis Services. In summary, we create a dimension using the dimUser table, and a measure group using the UserTerritoryBridge table. Such a measure group is commonly called a “factless fact table”. In both cases, we make their measures and attributes hidden; we don’t want users seeing them, they’re meaningless and there for security purposes only. Once defined, the cube structure looks like this ...

... and the dimension usage looks like this ...

With those structures in place, we can go back to our role, and modify it to be dynamic, i.e.; instead of creating a role per user/group, we now have a single role which handles ALL users. We do that by first selecting “Deselect all members” on the Basic tab, and then on the advanced tab use an MDX expression as follows ...

This expression uses the UserName() function to determine the current user, and uses that to filter out the Sales Territory Country members the user is not setup to view. Note the “Enable Visual Totals” checkbox down the bottom. This ensures the user can only see measure totals for the dimension members they can see. Without that selected, they will see totals for all countries, including those they cannot see when browsing the cube.

So that’s a basic implementation of SSAS dynamic security. Now comes the twist (with triple pike).

Consider an implementation of a PerformancePoint dashboard through SharePoint 2010 which uses the cube as a data source i.e.; a user browsing the cube through a PerformancePoint dashboard. In terms of user authentication/delegation, we have;

1. The user logs onto Windows,
2. The user connects to SharePoint,
3. SharePoint launches PerformancePoint services to invoke a Dashboard,
4. The Dashboard connects to Analysis Services

This is Kerberos heaven (or hell). There’s a lot of hopping there. More hops than a Kangaroo on a caffeine binge. There’s a lot that can go wrong, particularly when you add an ISA/Proxy server to the mix.

From a PerformancePoint perspective, there are now 3 authentication options for data sources;

Unattended Service Account (first option) is fairly straight forward – All users connect to the data source using the security context of the defined account. Simple, but not granular enough for custom security.

Per-User Identity (third option) solves this problem, assuming you have a working Kerberos environment.

An additional option is listed in the middle; “Unattended Service Account and add authenticated user name in connection string”.  As per the description, this adds the user’s login details to the connection string, and can be consumed within Analysis Services by using the CustomData() function, instead of the UserName() function, which is a nice little workaround for environments that are “Kerberos challenged”.

Now, something important to point out here is that this does not mean we can forget about Kerberos. As Richard and I discussed in the comments on his post, someone with access to the SSAS cube through a different front end client could potentially circumvent the intended security by altering the CustomData component of the connection string to connect as a different user. This presupposes a sophisticated user, but it’s a concern nonetheless. So at best, this is a temporary workaround for environments that ONLY use PerformancePoint as the front end for their BI solution.

To finish, I just wanted to leave you with this blog post from Adam Saxton, titled “My Kerberos Checklist…”

Enjoy :-)

Cheers,

Rod.

1. MVPs Greg Low and Arnie Rowland have each contributed one of their MSDN subscriptions, bringing the total to 5 so far,
2. We’re expanding the project to include New Zealand,
3. Telerik have kindly volunteered their Ultimate Collection for .NET product,
4. More details on additional training and learning products to come in the following days!

Further, we now have the links available for both under/unemployed applicants and non-profits to submit project proposals.

• Under/unemployed applicants, submit project proposals here
• Non-profits, submit project proposals here

More details to follow, all of which will be posted to the dedicated Project Phoenix page

In January this year, I received the MVP award for my contributions to the SQL Server community, joining an amazing group of people from around the world, each of which contribute to the community in their own unique way. Be it running user groups or websites, writing blogs or speaking at events, each of these people contribute their own time to make our community the vibrant and dynamic success that it is today.

This year, Microsoft rewarded MVPs with 3 MSDN Ultimate subscriptions to give away in any manner of their choosing. Each MSDN Ultimate subscription grants access to Visual Studio Ultimate, SQL Server, Windows Server and almost all other Microsoft software. I’ve wrestled with many different ideas on how best to give these away, none of which I was really happy with; each of these subscriptions retail at ~ $12,000 USD, so it’s not an easy decision.

Recently, I was inspired by MVP Arnie Rowland’s decision to create Project Phoenix, a program in which under/unemployed people are granted software, books & training materials in return for devoting their time to develop solutions for non-profit organisations. In Arnie’s own words ...

"...The idea is to provide the recipient access to all of the tools needed to improve his/her skills, an opportunity to gain practical experience, the potential to earn a recommendation and/or referral –and to positively contribute to society as a form of 'give-back'. No free lunch, just sweat equity –the kind that makes us all feel good for the effort..."

The program, based in the US, has been an amazing success, and looks to be gaining in strength. A truly inspirational idea from Arnie, and exactly the type of thing that makes me proud to be a fellow MVP.

I asked Arnie about the possibility of running something similar in Australia, and he enthusiastically supports the idea, so today, I’m announcing Australia’s own Project Phoenix, and to kick start the program, I’ll be donating my own 3 MSDN subscriptions. In coming weeks, I’ll be announcing more details, including qualification/award rules, and a formal application process. For now, this is a call to arms;

1. If you’re an Australian non-profit organisation in need of software/database development services, email me a description of the services required and your location,
2. If you’re from an organisation willing to contribute books, software, hardware or training, email me the details of what you’ll contribute,
3. If you’re an MVP willing to contribute your MSDN licenses to this program, email me,
4. If you’re a successful software developer willing to assist the award recipients in their development efforts, email me the support services you can provide

Rod.

Update; Project submission links and further details available here

1.    How many invoices did we process?, and
2.    How many purchase orders did we process?

The answer to the first question is easy. Every invoice in the fact table contains an invoice number so we can use a simple DistinctCount aggregate function to return the number of invoices as a measure. Note we use *distinct* count instead of count because the granularity of the fact table is the line item, so many rows will have the same invoice number.

Answering the second question (how many purchase orders) is a little bit more difficult. Not every invoice has a corresponding purchase order. “Direct invoices” are those that are paid without needing to have a pre-existing purchase order. In these cases, the Purchase Order column is NULL.

Back to the example at the start of the post; a distinct count in Analysis Services works differently than T-SQL. Where T-SQL will return 4, Analysis Services returns 5, considering NULL as something different than the other values. As a result, using the same DistinctCount aggregate function on the purchase order column will return one more than it should, assuming there are some invoices with a NULL purchase order value.

There’s a few ways around this problem, but the method I used was to create a new named calculation column in the data source view called NULL_PO_Exists with the following expression;

CASE WHEN PurchaseOrder IS NULL THEN 1 ELSE 0 END

Next, I created a new hidden measure called [Maximum NULL PO Exists] which used the MAX aggregate function over the NULL_PO_Exists column.

Next up, I set the visible property to false for the existing Purchase Order count measure called [Total Purchase Orders], and created a new calculation [Purchase Order Total] which used the following expression;

[Measures].[Total Purchase Orders] - [Measures].[Maximum NULL PO Exists]

In summary, the new measure is using the old measure as a base, and then subtracting 1 (if NULLS exist) or 0 (if no NULLS exist). A bit of fiddling around, but it works :-)

Finally, there’s a number of performance considerations when using distinct count. This white paper from Denny Lee does a great job of explaining the problem and a number of best practices for optimizing its performance.

Cheers,

Rod.

Cheers

1: Case Sensitivity

I’ve never been particularly good at using case consistently, the perfect example of which was my publisher asking me to edit hundreds of figure captions in my recently published book. Like most people of my age who took a college course in programming, I studied C, and spent countless hours debugging problems traced back to the incorrect case of variable names (and using “=” instead of “==” !).

When I decided to specialize in SQL Server administration, I rejoiced at the lack of case sensitivity involved, although I occasionally see databases with the case sensitive option, in most cases (pardon the pun) installed my accident.

Imagine my angst when I discovered that a tool I’m spending more and more time with is rampantly case sensitive. Almost everything about SSIS is case sensitive; the expression language, variable names and one that gets me every time; lookups.

A very common SSIS data flow transformation is lookup. As the name implies, it’s most often used to lookup a key value based on a text value in the data flow. For example, in the figure below, we’re returning the supplierKey based on the supplierName value. The first screen defines the lookup source, and the second defines the join condition and what value is to be returned.

This, for all intents and purposes, in a simple join condition with a column from the joined table being returned. However, unlike T-SQL, the SSIS lookup is case sensitive.

The obvious way to address this is to ensure both the lookup column (vendorName in this example) and the matching data flow field (Supplier) are set to the same case using the UPPER (or LOWER) function. For the lookup, that’s a simple case of changing the lookup source T-SQL to use one of those functions. For the data flow field, we could use the derived column transformation using the same function, and use that column in the lookup process.

2: Strong Typing

If there was any remaining doubt that SSIS was designed for developers (and not classic DBAs) it’s confirmed in its (very) strong typing. As an example of that, let’s revisit the lookup example from above. A previous version of the database had the Supplier data type set to varchar(50) and vendorName set to nvarchar(255). When we try and connect these in the lookup, we’ll get the following error;

Another common issue with mismatched data types is when inserting rows into a table at the end of a data flow task. If the destination table’s column length is shorter than the source, you’ll get a warning such as this one;

Unless you have the luxury of an enterprise wide data dictionary (that every one adheres to), data type variances such as these are common, with the most frequent SSIS workarounds being to either CAST the columns in the data source selection (e.g.; using a view) and/or using type casts in a derived column transformation (more on that in part 2 of this blog series)

3: 64-bit Trickery

There will presumably come a day when everything works on 64-bit, and that day can’t come soon enough. In the meantime, we have to deal with a variety of annoying problems, one of which is 64-bit driver support in SSIS.

A common data source for SSIS packages are Excel files. The current (and all previous) versions of Excel do not include 64-bit drivers. This presents a problem when SSIS packages run in 64-bit environments, and the error messages returned are far from helpful (unless you consider “The AcquireConnection method call to the connection manager blah failed with error code blah” helpful).

Fortunately, there’s an easy workaround for this issue, via a property called Run64BitRuntime accessed through the Debugging page of the project’s properties window. As per the figure below, setting this property to False (it’s true by default) will use the 32-bit driver.

Note that there are a whole range of other considerations for 64-bit mode, for example when calling child packages from a parent with a different setting for this value. For a great blog post on these issues visit Todd McDermid’s post  from last year.

4: Package Configurations

One of the real SSIS horror stories that I was very close to (but not responsible for!) was an SSIS package that was run against a production database by accident (blowing away gigabytes of production data). The package used a production configuration file instead of a test/development file. There’s a couple of aspects to this (all too common) problem; configuration technique and security.

Firstly, configuration technique. SSIS allows a number of ways of configuring settings such as server and database name. The most common one is to use an XML configuration file, the path to which is either stored within the package itself, or set through an environment variable.

I’ve seen problems with both of these techniques. Using a configuration file location (as per the example below) assumes that all servers on which the package runs has the same path available, which is quite often not the case.

The environment variable option can also present some problems, notably the need to reboot the server for the variable to come into effect. Depending on the environment, rebooting production servers is not the easiest thing to do.

Another alternative to both of the above is one that I discovered while listening to Greg Low’s SQLDownUnder podcast with Jamie Thompson  in which Greg spoke of the idea of connecting to a special configuration database which stored the configuration settings. The settings returned were based on the calling machine’s context and then used to set the value of package variables for subsequent use.

Regardless of the configuration technique, one of the really important considerations is security. In each of the above techniques, mistakes can still be made. For example, a production configuration file can be copied over the top of a development file, or the incorrect settings stored in a configuration database.

To prevent these types of configuration errors from killing a production database, we need to ensure that the security context of an SSIS package prevents it from accessing the wrong environment. There are at least two ways of achieving this; using separate service accounts in each environment (and making sure the account only has database permissions in the appropriate environment), or better still, having the production database in a separate domain with trust permissions removed to the other domain(s).

With the appropriate security setup, even if the wrong configuration file is used, the package will fail to run due to the lack of database permissions.

5: Miscellaneous Silly Business

Finally, there’s a number of really silly things I continue to do that I shouldn’t (I’m a slow learner). Firstly, the evaluate as expression property.

A common design pattern in SSIS packages is to use a variable as the source for an Execute SQL task. The variable’s value is then set using an expression which references another variable. A common example of this is a variable that contains an update command with a where clause containing the value of a BatchID variable. The expression property contains the code that references the other variable.

If you use this type of arrangement, make sure the variable's evaluate as expression property is set to TRUE, as per the example below. I’ve spent many hours debugging problems where I’ve simply forgotten to set this properly.

To wrap up this post, I frequently want to add an existing .dtsx package (e.g.; from another project) to the current project. By default I right click the SSIS Packages node in solution explorer and choose the .dtsx package that I’ve already copied into the project directory. I’m then confused as to why the package is renamed e.g.; from Package.dtsx to Package(1).dtsx.

It turns out that the Add Existing Package menu is really designed for adding a package from a different location (i.e.; remote server), and if you use it locally, it makes a copy (and renames) the file.

For the situation I described, using the Add > Existing Item option (instead of Add Existing Package from the SSIS Packages menu) is the correct option. The figure below compares these 2 options side by side.

In the next post, I’ll talk about some important SSIS performance tuning tips.

Cheers