Today's blog is about using PowerPivot to solve real-world problems.  

While PowerPivot hasn't hit the street as a shipping product yet, I'm intrigued by it, and I think it has a good future.  However, I see its value differently than some.  

I see it as th tool you use to add a degree of flexibility and agility to a larger data warehouse strategy. Sure, I see how it can be used to meet entirely standalone needs, but I don't think that's where its greatest potential lies.

No matter how well we plan, real-world requests and requirements always seem to stay just ahead of us, and there's always a creative power user out there ready to build a data mart with Excel and VBScript to fill those gaps.  

Wouldn't it be great if we could have a flexible tool to fill in the (hopefully) small number of requests that our data warehouse can't yet meet, but do it in a way that preserves the integrity of what's already in production?  I think PowerPivot may be that tool, and I think you should take a look.

The below embedded video walks through using PowerPivot to turn around an analysis request that doesn't quite meet the structure of an in-place data warehouse.

Note: You can view this video full screen by pressing the full screen button on the bottom toolbar. It's the second item from the right-hand side.

Recently I had a question from a client whether there was really any performance improvement in SSAS cube processing when using partitioning strategies vs. just using a single partition.

While intuitively I "knew" that partitioning cube fact tables results in better parallelism and thus increased performance (when adequate CPU, memory and I/O resources can support it), I realized I really didn't have any empirical "proof" to backup my professional opinion.  So--I setup a simple test.

The following is a very simple comparison of processing the same data set with a cube design that's identical--except for the introduction of multiple partitions. For this test I used Analysis Services 2008R2 x64 running on a 4-core Xeon server

The data set processed includes 37 million fact rows, and several dimensions--the largest including around 4,000 members.  In the first test, a single partition is used.

In the second, the cube is partitioned along months, resulting in about 1.3 million rows per partition.  This is below the recommended threshold for partition sizing, but provides a decent (if basic) evaluation of the effect of parallelism on cube processing.

Results:

• Time to process the cube using a single partition: 21 minutes
• Time to process the cube using 23 partitions: 12.7 minutes
• Bottom line: by partitioning the processing of the cube, there's a time savings of around 40% in this case

Today's blog is about connecting Internet/Extranet users to a PerformancePoint solution that uses Kerberos delegation to pass end-user credentials through the application layers to back-end databases. This article is about how to do it in a way that doesn't require VPN deployment, is easy to use and convenient for end-users, and adds no additional burden on SharePoint administrators or DBAs.

What? Impossible you say?  Not at all.  In fact it can be relatively easy to implement without the commonly suggested security trap-doors.  The technique has really been around for quite a while, and it's accomplished through the use of a reverse-proxy solution such as Forefront TMG or ISA server (Forefront is the name of the latest version of the product formerly known as ISA Server).

The video below is an overview and demonstration of a working solution combining the following components.

1. Windows Server 2008 R2 x64
2. SharePoint Server 2010 (CTP)
3. PerformancePoint Services (part of SharePoint 2010)
4. SQL Server 2008 R2 (CTP)
5. Forefront TMG 2010

Note: You can view this video full screen by pressing the full screen button on the bottom toolbar. It's the second item from the right-hand side.

At BlueGranite we've been considering the best way to provide better visibility to all the great capabilities BI and Dashboards deliver.

More often than not, when I do an in-person demo of a dashboard solution, it's the first time the audience has seen this kind of solution before--except for maybe a screen print or a sketch.  Personally I don't think an interactive dashboard can be explained or even understood by seeing a screen print! 

 So, today I made a quick (5 minutes) demo and uploaded it to YouTube...kind of a test of whether this is a good format to share quick ideas and demos.  Afterall, there are only so many opportunities to visit in person, and who can be everywhere at once? (hopefully with the Internet, I can?)

The video below demonstrates a BI solution which is a reduced scope demo of a solution our firm implemented in the transportation (trucking) industry.  The technology is primarily SharePoint, PerformancePoint, SQL Server Analysis Services, Silverlight and Bing Maps.

 If you like this way of communicating/blogging for BI demonstration and best practice, let me know.  It takes more time than writing, but if it's useful then in my view it's worth the time.

 --Rob

BI Mashups

Tuesday I attended an interesting session presented by J.R. Arredondo and Dave Pae about putting together Enterprise Mashups using SharePoint Designer for WSS or SharePoint 2007. 

Mashups are one of those buzzwords, not unlike Twitter, FaceBook, etc., that sounds like a cool technology my 13-year old would be interested in but which I always try to approach with a bit of skepticism given my focus on business solutions that have ROI requirements.

And so it has been with "mashups" for me...on the radar but a bit unproven in terms of business value.

I think that needle has moved for me as a result of this session.  Of course, my primary focus is whether each new technique or technology is relevant to real-world BI solutions (not just something entertaining during a demo).

So how do I see mashups extending a traditional data-driven BI solutions?  Well, the ideal would be to take (A) traditional, planned data in a database or cube; (B) add in unstructured data (like sharepoint lists), and (C) access information on the web or from LOB systems using web services.

Modern BI solutions like PerformancePoint solve A+B, but C is not usually in the realm of end-users or analysts who assemble BI solutions.

While the Arredondo & Pae session didn't address a BI environment directly, I can see quite well how to adapt their techniques to do some interesting things. Using PerformancePoint we already have the ability to link to non-BI components, and by combining this with SharePoint designer mashup capabilities I can easily see integrating maps, internal web services and public services via various protocols.

If you haven't looked at Microsoft's Mashup page, take a look at it here: Enterprise Mashups.  And if you're not aware that SharePoint designer is now free, download it here and take a look at ways to design rich mashup pages in sharepoint.

Today I had the pleasure to attend Donald Farmer and Kamal Hathi's session on the Gemini project.  I've seen several of Mr. Farmer's presentations on BI and Data Mining, and I'm never disappointed at his skills and depth of knowledge!

Gemini is an in-memory, Excel-based, analysis services orientated technology that brings the power of dimensional modeling to a primarily Excel skilled audience--with the promise that the output of Gemini can be forward-engineered into managed Analysis Services solutions. 

Donald's quite entertaining slide deck was a major departure from the typical corporate spin that we're accustomed to seeing (and that I'm personally accustomed to producing) when we educate potential technology users.   

The slide deck casts the analyst, boss and IT admin as silent movie characters, where the analyst and IT professional struggle to keep the boss happy within the limitations of their skills and the existing content in the corporate DW.  It's the all-too-familiar "I need that analysis by tomorrow" versus "it takes time to put that data together".  Terribly apt and consistent with what we see in the "BI trenches" every day.

The silent movie theme stresses that this situation hasn't changed that much in many decades (and in any event in the two decades I've been working on BI solutions).  Of course, the slides were endlessly entertaining while driving home these points!

As for the "Gemini" product, it's quite exciting.  BlueGranite has been in the business of helping our clients' analysts pull info together quickly and make it meaningful since the ProClarity days.  One of my lingering concerns has been that--since ProClarity was acquired and the future of its core technology became uncertain--what tools within the Microsoft product suite would serve these "data diggers"? 

Excel pivot tables against SSAS cubes are fantastic--but how to make sure analysts can get data pulled together by the cube designer fast enough to meet business needs?  If the corporate governed DW/Cubes evolve slowly, how can analysts fill in the gaps? 

Today I still recommend ProClarity for the deep data analysts (as a complement to Excel), as it still provides more flexibility and richer visualization than Excel pivot tables for advanced analysts.  But I think Gemini may one day (hopefully soon) deliver even more, and really become the data digger's tool of choice.

Gemini is clearly intended to fill the analyst gap.  And in so many ways I think it will. It's at once a more approachable way for non-OLAP users to build rich dimensional models, a way to make more dynamic data integrations using the familiar Excel environment, and a rich OLAP query tool to be used against these analyst-generated models.

In truth, I can easily see even seasoned SSAS pros (including myself) using Gemini during prototype and early development, in addition its intended less sophisticated audience.

The things I love about Gemini:

1. Users import data from all types of sources:
   a. Structured relational
   b. Existing OLAP Cubes
   c. Subscribe to "Service Documents" (a form of RSS feed that contains tabular data)
   d. Paste in any tabular data (e.g. copied to clipboard from a web page)

2. Fast processing of large data volumes (100M rows demonstrated, 20M demo'd on a netbook)

3.  Ability to add calculated measures at the Excel pivot-table layer (sweet!)

4.  Ability to connect to Gemini models as SSAS data sources (rocks!)

5.  Tight integration with SharePoint as a basic architectural construct

6.  Translation of most OLAP concepts to Excel terminology more familiar to analysts

Some things I would like to see improved, or clarified. 

I would have asked these questions, but Q&A was cut short today due to time constraints...

1. As simple as they seem to MDX people (like me), my sense is the DAX expressions are going to be too complex for many of the analysts I train.  It reminds me of PPS-P PEL, in that the expressions are intended to be simplified from MDX, but they're still complex and require multidimensional thinking--which isn't a gift many of us are born with.   PEL generated lots of push-back from analysts when I demonstrated it to "real customers", and I fear DAX may as well.

2. To address #1, I hope the product team will consider following the model ProClarity set with it's KPI Designer, which allows users to use wizards to build calculated measures such as ranking and bubble-up exceptions.  KPI designer users build complex MDX without knowing that's what they're doing.  I still train new users on these tools, and it's a positive for them to use wizards (rather than purely language constructs, as with DAX).

3. I'd like to see some MDM tie-in to ensure that already accepted calculations, data sources, etc., can be drawn upon and re-used in a Gemini solution.  I can see a BI governance issue (and IT objection!) if many analysts are building silo BI solutions without some centralized baseline to start from.  Gemini doesn't prohibit a DW/MDM baseline, but it doesn't appear to promote one either.

4. Security really wasn't addressed in the session.  If analysts will be pulling 100M rows of fact data into a desktop solution…how does a corporate IT policy ensure that that such huge volumes of data isn't lost in the back of a taxi?  BitLocker would be a convenient answer to this question, but I hope it isn't the only one.

5. Data mining--not sure if it's possible to incorporate DM models into the Gemini models, but the combination of capabilities on the desktop would be really fantastic!

In-memory OLAP is a hot technology, and as illustrated by Donald's slides, the world has been waiting far too long to put such powerful tools in the hands of typical analysts.  I can't wait to see how this possibly disruptive technology impacts the wide swath of users its intended to benefit!

Recently I've been working on deployments of Windows Server 2008 and SQL Server 2008.  I thought I'd start to post some of the nuances of these new product editions. 

One of the first things to encounter is, not surprisingly, security.  Security is always loads of fun for deployments (ha ha), but actually I kind of enjoy the challenge of working within the confines of good security practices.

Windows Server 2008 is based on the Vista core, and inherits a lot from it.  One of these is the Windows Firewall.  I think this is a really good thing...having that extra layer of security is definitely wise, and is a nice blanket I actually miss from my Sun/Linux days--so I'm actually glad to have it there.  But, it also means you have to configure security for just about every new application, port, etc.

For SQL Server, this really isn't too difficult.  However since I do so many deployments, I'm always interested in a shortcut...and I like to document changes, get them approved by client syadmin, then apply them by script whenever possible. 

So, below is a sample script I put together for applying firewall changes needed by SQL Server 2008 when running on Windows 2008. This is a work in progress but so far so good. Note that this script opens just about every port SQL Server might use, so make sure to use only those lines that apply to any given server (e.g. don't open HTTP/80 if you're not running anything reporting services, etc.).

Of course, if you're using named instances for SQL Services, those instances by default will have dynamic (i.e. random) ports.  Dynamic ports don't work that well with a server firewall (and neither do they work well for Kerberos delegation configurations--but that's another topic).  So, a best practice is probably to set static ports for each instance and manage them that way.

One thing to make sure to do if using command lines like this is to specify rule names on the command line, then those names show up in the GUI-based firewall control panel (firewall.cpl)--see the screen grab down below. If you don't--then each rule will simply be named "unspecified"...not a nice thing to leave for the sysadmin to figure out later!

@rem firewallconfig.cmd by Rob Kerr 

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"

@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"

@echo Enabling conventional SQL Server Service Broker port 4022 
netsh firewall set portopening TCP 4022 "SQL Service Broker"

@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"

@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"

@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"

@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"

@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"

@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

When planning a deployment of PerformancePoint Monitoring & Analytics (PPS M&A), an often overlooked component in the business solution is the ProClarity Analytics Server (PAS) application layer.  

PAS is sometimes not deployed by customers, and their analysts aren't trained in the effective use of these analytical tools. In my view, this is often a mistake.  Analytics has at least two constituencies--and all need to be considered when planning a successful BI deployment.

A sales specialist I have a great deal of respect for, Mac Hussey, once explained it this way...this isn't a direct quote but my interpretation of his ideas: PerformancePoint server M&A's native scorecards, analytic grids & views cover "Little a" (lightweight analytics), while PAS covers "Big A" (heavy analytics). 

Two A's...so what's the difference? "Little a" is flashy, cool and as easy to use as a web page--often requiring no training beyond a quick overview.  In most industrial and service industries, perhaps 85% of BI dashboard users are executives, managers and line of business users.  These users don't use analytics in their primary job function, but use analytics to support their "real" jobs. 

Even in the long-run, they may only want/need "Little a". If your organization fits this mold, it may seem that reaping the low-hanging fruit using "Little a" is really where all the value lies--and that's certainly true at first. Eventually, though, the "Little a" users want to know root causes, and analysts really need to dig and develop further insights.  That's when "Big A" is needed (ProClarity Analytics). 

"Little a" is certainly a "low hanging fruit picker" and brings in quick ROI in for any BI dashboard deployment.  Yet time spent deploying and training on "Big A" is where the game-changing ROI comes from over the mid/long-term. Currently this means deploying an additional application (PAS) and training end-users.  

Eventually I'm sure we'll see ProClarity Analytics fully integrated into PerformancePoint, and the "ProClarity" product brand will be just a fond memory. When that happens, every user will have access to both the "Big a" and "Little a", even if--as with Excel's features--they only know how to use the most common analytical capabilities offered by the product.  Until then, Microsoft BI practitioners who address the needs of users in the "here and now" will need to continue to pay close attention to the spectrum of user needs when planning BI deployments.

Microsoft’s security focus over the last few years has brought many improvements in the overall level of security.  Along with this level of security also comes an increasing number of hoops to jump through to accomplish simple tasks.

Recently I found that it became impossible to install software from network shares onto Windows 64-bit servers.  Trying to do so resulted in the error message:

“Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item.”

Searching on the Internet, the most commonly suggested workaround I found was to uninstall the Internet Explorer Enhanced Security Configuration from the computer.  This actually does work, but really doesn't sound like a great solution to me. 

As it turns out, the solution is quite simple...but also a bit of a head scratcher.  You wouldn’t think accessing files on your LAN would be controlled by an Internet Explorer setting (well maybe you would, but I wouldn’t!).  Yet, changing IE settings is the easy way to solve this problem.

The solution is to add the LAN server where your install source share is to the Local Intranet zone in Internet Explorer.  I suppose adding to the Trusted Sites would probably work, but since a share location is in the intranet for most of us, I think that’s the more logical place to make this change.

Here’s how I solved it on a 64-bit Windows 2003 R2 server with SP2 and IE7:

1. In Internet explorer, select Tools/Options, then click on the Security tab
2. Select the Local Intranet icon, then press the Sites button
   
   
   
3. In the Add this website to the zone textbox, enter the name of the server in the form file://servername, then click the Add button
   
   
4. Click the Close button on the bottom of the dialog, then OK on the Internet Options dialog

With that change made, your server should now be able to run software from network locations.  No more need to copy them to a local file location first!

In this article I'll provide some basic guidelines for implementing X.509 PKI-based e-mail encryption that you can implement for free!  The goal for this article is a process you can use with your business associates and/or friends to exchange e-mail securely, with no additional software to install, and no costs to anyone in the process.  Sounds too good to be true?  Not at all!

Usually we send e-mail over public networks--via ISPs that guarantee no level of confidentiality.  We've probably all received passwords or other sensitive information via Internet e-mail at some time in the past.  Have you ever thought to yourself, "wouldn't it be great if I could easily send this e-mail in a way that only the recipient could read it?" 

Probably the most popular mechanism for sending encrypted is the proprietary--and until recently free--PGP client software.  I've long been a fan of PGP, which is not just good for e-mail privacy, but also for transmitting data securely.  But for e-mail encryption, PGP is traditionally a little nerdy to use, and I can't say I've ever had a business contact ask me to send him or her a PGP key.  The newer commercial versions of PGP clean up the clunkiness quite a bit, but installing PGP client software is a big step, may be prohibited by company policy, and even though $99/user isn't going to break the bank, a free alternative would be easier, right?

 X.509 Public Key Infrastructure (PKI) based encryption has been available for years, but in my experience is rarely implemented.  Perhaps the primary reason is complexity, low perceived need-level and/or inconvenience. 

Recently I took a look at using X.509 PKI keys in conjunction with Outlook 2007, and I was amazed at how simple it was to implement--at least at an informal peer-to-peer level. Although I'm using Outlook 2007, the ability to use certificates to send/receive encrypted e-mail isn't new with the 2007 version, and this basic approach will work with virtually any Microsoft e-mail client, and in fact most other e-mail clients as well.

Let's look at how to implement this solution. Setting up X.509 within Outlook 2007 has a few requirements:

1. Both the sender and receiver need public/private key pairs from a trusted Certificate Authority (CA).
2. Each party must have the private key installed on their own Windows PC
3. Each party must have the public key for the other party in his/her local certificate store 

Once everything is setup, the process is really pretty simple:

1. The sender uses the recipient's public key to encrypt the e-mail message as it's sent
2. The receiver uses his/her own private key to decrypt the message

We'll return to getting "everything setup" in a minute, but first a little bit more about why this process works... 

PKI e-mail certificates work because anyone can encrypt a message using the recipient's public key, but only the recipient's private key can reverse the process--yielding a decrypted message.  So the public key can be distributed to anybody without compromising the solution.  Only the accidental release of the private key will compromise the security of the transaction.  If this sounds like SSL (the technology used to encrypt your credit card on amazon.com), that's because it's almost exactly the same.

So, you ask, does that mean I have to buy an SSL certificate for my e-mail program--just like I do for my web servers--and all my e-mail recipients have to do the same?  Well, yes--and no.  Just as with SSL certificates, anyone can issue them.  You could create your own certificate, and your associates could create their own as well. The problem is that if you issue your own certificate (i.e. you become your own CA), you need to convince everyone you send e-mail to to trust your self-signed certificates.  Actually it's not your friends you need to convince--it's their computers. 

So, for self-signed certificates to work seamlessly, you'd have to get all your recipients to install your root certificate into their computer.  This is a reasonable thing to do if all users sending/receiving e-mail are within your company, but when it comes to external recipients, self-signed certificates may well be impractical. Just as with web site SSL certificates, it's easier to use a commercial CA that everyone else already trusts.

So, to exchange e-mail securely with external associates, the best option is to use certificates issued by a commercial, trusted CA.  These certificates are fairly inexpensive, and can even be obtained for free from Thawte and Comodo.  There are some good reasons to buy blocks of certificates from these companies and roll them out in your company.  However, for our informal solution, we'll just use free certificates from Comodo. 

OK, enough background.  Let's get this implemented.  Here's the process:

1. Go to Comodo's web site, and obtain a free secure e-mail certificate.  Just follow the instructions (which I won't go through completely here).  At the end of the process, the certificate will be installed in the certificate store on your Windows PC. 
2. Have the intended recipient(s) of your encrypted e-mail also obtain secure e-mail certificates.  It doesn't matter whether everyone uses the same provider (Thawte, Comodo, Verisign, etc.).
3. Send each of your intended recipient(s) a signed e-mail.  This is easy to do--just click the "Digitally Sign" button in the Outlook 2007 Options ribbon bar section.  Your recipient(s) should also send you a signed e-mail button.
4. Sending the signed e-mail messages is just a way to exchange public keys (remember, you need to have the recipient's public key to encrypt a message before sending it to him/her).
5. When you receive a Digitally Signed e-mail, right click on the 'From:' e-mail address, and add the contact to Outlook.  If your recipient's contact information is already in Outlook, accept Outlook's warning and let it update contact information. This will add the public key to the existing contact record.  Your recipient should do the same with your signed e-mail so he/she can encrypt e-mails for you.

Now that you have your recipient's public key, and your associate(s) have yours, all you have to do is click on the "Encrypt" button in the Outlook 2007 Options ribbon bar section before you send the e-mail.

That's it! Now you and your associates can send encrypted e-mail to each other anytime you want.  In fact, it's so seamless you probably won't even be aware that the e-mail you receive is encrypted--Outlook will automatically decrypt e-mail for you as you read it.  If you want to verify that the process is actually working (and of course you do!), there are two simple ways to know a received e-mail is encrypted:

First, the e-mail reading window will have one or two icons: the first looks like a padlock, and indicates the message is encrypted.  The second looks like a 4-H ribbon, and indicates the message is signed.  These are the same icons used in the Outlook tool-bar.

The second clue that a message is encrypted is that Outlook can't display it in the preview pane:

Now that your certificate is installed, whenever you have a request to send or receive encrypted e-mail with an external associate, simply exchange public keys (by sending digitally signed messages to each other), and you're on your way!