The WorkerThread Blog

we know stuff so you don't have to

Archive for the ‘SQL Server’ Category

SQL Server 2012 – Fixing Up Post-Patch Issues

Posted by workerthread on October 11, 2012

I have an “all in one” virtual machine for demonstrating the Business Intelligence features of SQL Server 2012 in combination with SharePoint 2010.   My VM is based on the “Kiwi Build” setup documented in the TechNet Wiki article here (although mine is using VMWare Workstation instead of Hyper-V).  As well as all varieties of Analysis Services (Tabular, MultiDimensional, PowerPivot) my VM also runs SharePoint, Master Data Services and Data Quality Services.

Since the initial release of SQL Server 2012, there have so far been three Cumulative Updates and this week a very large security update.  I generally check these patches out using my VM and each time I’ve come across specific post-patch issues which need to be addressed.  I thought it would be worth sharing these here, both for my own future reference and hopefully to assist others.   Note that these issues are my experience on my VM – you may encounter very different problems when you apply the patches, most of which are caused by the updated assembly versions that the patch installed – I probably can’t help you with those.

So this week, as usual, I first took a snapshot of the VM, then applied the security update. This installed without problem and I could see following the update that all of the expected SQL Server services were running as was SharePoint Server.   The post-patch fixing up is only needed once you start using the various SQL Server services –

PowerPivot Service in SharePoint

First I went to a PowerPivot Gallery in SharePoint, opened a workbook to display a pivot table with slicers.   When I clicked on a slicer to filter the data, I immediately got a message saying “an error occurred during an attempt to establish a connection to the external data source” –

PowerPivotError

To fix this and get PowerPivot working again, you need to re-run the PowerPivot Configuration Tool.   When this loads it checks the current farm status and in my case returned the following message telling me “newer versions of the PowerPivot solution files have been detected” –

PowerPivotConfigMsg

And as you can see, the “Upgrade” option is already selected –

PowerPivotConfigTool

I did as I was told, ran the “upgrade features” option and once this completed, the error message had gone from SharePoint and I could once again use my published PowerPivot models.

Data Quality Services

Next I opened the Data Quality Services Client and attempted to make a connection to the server.  I got a very verbose error message essentially telling me “an error occurred in the Microsoft .NET Framework while trying to load assembly id 65627” –

DQSError

A quick online search led me to this document which discusses installing Cumulative Updates or patches on Data Quality Services.   It doesn’t explicitly mention assembly ID 65627 (although other online articles do) but as it says, you do have to run DQSInstaller.exe – upgrade to fix the issue above which did the job for me. 

Master Data Services

Finally (for me anyway) Master Data Services.   When I opened the Master Data Manager web client, the message I got was “the client version is incompatible with the database version” and a request to update components using Master Data Configuration Manager –

MDSError

So, off I went to Master Data Services Configuration Manager, connected to my MDS database, and got the same advice about upgrading the database –

MDSConfig

Which I duly did, and once complete Master Data Services and Manager were back up and running (actually I think this is a little misleading because the original MDS error suggests that the client version is out of step, but the database upgrade fixes it).

So there you have it.   From what I’ve seen so far all or some of the above steps will be needed after patching SQL Server 2012, depending upon which features you have installed.   At the time of writing I haven’t tried applying Service Pack 1 which is currently at CTP level but if anyone has, please leave a comment.

Update December 2012 – I can confirm that after applying SQL Server 2012 Service Pack 1 it was again necessary to complete the steps outlined above fix the issues.   As Cumulative Update 1 for SQL Server Service Pack 1 arrived fairly soon after I also tested this and found that SP1 CU1 didn’t cause anything to break.

Advertisements

Posted in Microsoft SharePoint, PowerPivot, SQL Server | 2 Comments »

SSAS Cubes, Excel and SharePoint – Keeping Contributors Happy

Posted by workerthread on January 10, 2012

OK, I know this isn’t exactly cutting edge stuff and we now have PowerPivot, the forthcoming Power View, tabular data models and more in SQL Server 2012 but let’s face it, there are a great many organisations for whom none of these new features will actually be in production in 2012.  These organisations have invested in SQL Server 2008 R2 and spent time in 2011 building SQL Server Analysis Services (SSAS) cubes from their line of business data – and getting SharePoint 2010 up and running.  Now they want to get a return on their investment before they spend more on licences, upgrades and more hardware. 

Now Excel pivot tables and charts aren’t perfect, certainly not cutting-edge.  But if the infrastructure is in place then using Excel 2010 as a client for your SSAS cubes and publishing workbooks or workbook items to SharePoint 2010 is an excellent “self-service BI” entry-point.  In my opinion Excel 2010 is still the best choice for validating the quality and accuracy of the data in your cube and I often get users who know what to expect from the data to play a major part in this task.  And of course Excel Services pivot tables and charts can become part of the bigger BI picture later as a part of PerformancePoint dashboards. 

However you still need to make it as easy as possible for your “self-service BI” contributors to find their data and publish it.   This is what I’ll cover here.  Note that I’m not intending this post as a guide to creating Excel reports with SSAS Cubes and SharePoint (see links at bottom of post for various walk-through examples and tutorials).  My main objective is to show how to put together a SharePoint environment which your Excel Services user population (both contributors and consumers) are comfortable using.

Before Your Contributors Get Started

To kick off, let’s briefly cover some things that need to be in place before you let your Excel Services contributors loose (all of which assumes you are working with Office 2010 and SharePoint 2010):

You need SharePoint Server 2010 Enterprise – I mention this because I still frequently see the “which SharePoint version do I need” question asked online.   Excel Services is a feature of SharePoint Server 2010 Enterprise, so if you have SharePoint Foundation or SharePoint 2010 Standard you are out of luck.

You need Office 2010 Professional or higher – many of the Office 2010/SharePoint 2010 integration features aren’t available in Standard Edition.

Decide where your Office data connection files will be located in SharePoint – all of your published Excel workbooks that connect to SSAS cubes should use an Office data connection (ODC) file stored in data connection library.  If you are using the default SharePoint Business Intelligence Centre site collection template then you will already have a Data Connections library in place.  If you are using your own site you will need to create a Data Connections library – and don’t forget that data connection files created in these will by default require approval once created and uploaded, so make sure that this isn’t overlooked.

Decide where your Published Excel workbooks will be stored –  I normally create a specific document library for these (and as you will see later, may well enable content types).  

You must configure Trusted File Locations and Trusted Data Connection Libraries in your SharePoint Excel Services application – This is frequently overlooked and needs to be planned and configured in advance.  If things aren’t configured correctly here then your Excel workbooks may not open in the browser (trusted file location not configured) or refresh data connections (trusted data connection libraries not configured).   TechNet has more details on this here and there is a good troubleshooting guide available for download here.

Assign appropriate permissions to the Excel document library in SharePoint – you need to assign Contribute permissions to the group of users who will be allowed to publish workbooks (this gives them permissions to view, add, update and delete workbooks).   Typically I recommend other users be assigned View Only permissions within the library as this ensures they can view the published workbook items in a browser but only download a snapshot containing values and formatting (users with next-highest Read permission can open the workbook in Excel and view all underlying details).  If your Excel contributors are also creating/editing dashboard pages containing Excel Web Access components then you will need to assign Design permissions to them.

Assign appropriate permissions in the Analysis Services Cube – if you are using Integrated Windows Authentication to connect to your cube, you need to do more than grant appropriate SharePoint permissions.  All users connecting to the cube via Excel and SharePoint will also need to be given permissions to access the cube itself.  I would recommend that when the cube is designed in Business Intelligence Developer Studio, a “cube readers” role is set up and an Active Directory group is assigned the appropriate permissions, then all that is needed is to add existing and new users via Active Directory.

Making Data Connections Easier

I’m assuming here that your Excel contributors are already familiar with pivot tables and charts and may already have connected to a cube themselves.   However, connecting using an Office Data Connection file stored in SharePoint may well be new to them and the process of creating a new analysis and re-using the existing data connection isn’t that intuitive.   Here are some suggestions:

Create the Data Connection files for them, before they get started – you won’t normally need a lot of data connection files (typically one for each cube or cube perspective) so I would absolutely recommend getting the data connection files created, published and approved before your contributors get to work.  Here is a Microsoft Office article on creating and using Office Data Connections – it dates back to Excel 2007 but most of the instructions still apply.  The article does talk about using a “DataConnections” web part.  Just to clarify, what they mean here is view of your data connections library which in the article is called DataConnections.  Below is an example in SharePoint 2010 where a view of approved ODC files in my  DataConnections library is shown on a site page (click image to open full-size) –

data connections list - click to view full size

The default action that will take place when clicking on either of the links is for a new Excel workbook to be opened with an empty pivot table created, using the stored data connection you clicked.

Consider using Content Types in your Excel document library – this is another way of making it easier for your contributors to create new cube-connected Excel reports.  There are already a great many online tutorials on how to create and use SharePoint content types (here for example).  What I’m suggesting here is that the document library which will be used for your Excel workbooks has “allow management of content types” enabled.   Each content type added to the library can then use a different Excel document template which already contains a data connection pointing to the appropriate Office Data Connection file that you’ve already uploaded.   Your contributors then just need to select the appropriate new document from within the library:

New Document from Content Type

then create a new pivot table in the workbook and select “Use an External Data Source”:

Create Pivot Table

and choose the existing connection in the workbook:

Existing Connection

So the contributor creating the workbook doesn’t need to find the connection file, and you have the added advantages of being able to add other metadata columns to your content type and of course re-use in other libraries.  Additional metadata columns are particularly useful if your library is likely to contain a large number of Excel documents – they will allow you to create custom list views of your reports by type, department, owner etc.  Something else you may find useful here is enabling content rating on your document library.

Make Sure Published Workbook Items Refresh

One other quick point to note before I finish this post – I frequently see published workbooks which don’t show the most up to date data when opened in the browser.  It’s important to ensure that workbook connection properties have “Refresh data when opening the file” checked, otherwise you will be looking at stale data:

ConnectionProperties

Links for those Getting Started

Here are some links you might find useful if you are just getting started with Analysis Services,  Excel and SharePoint –

Getting Started with Business Intelligence in SharePoint Server 2010 (well worth downloading the PDF or Visio document)

Excel Services in SharePoint 2010 Dashboard Improvements

Excel Services Data Refresh Troubleshooting Flowchart

Connecting to Cubes and External Data with Excel in SharePoint (excellent blog post from John White)

Excel Services Module of SharePoint Server Business Intelligence Insights Training – good info in this video series and you can download the accompanying Lab02 here.

That’s it for now – I hope the points I’ve covered help you with a successful SharePoint BI solution.   If you’re interested in more posts on this topic leave a comment and I’ll see what I can do.

Posted in Business Intelligence, Office, SQL Server | 3 Comments »

Book Review- MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook

Posted by workerthread on August 31, 2011

There has been a big buzz around all of the new Business Intelligence features coming along with the next release of SQL Server, currently codenamed Denali.   This release is going to have a huge number of new features, not least of all two different versions of the Analysis Services engine – the original multidimensional OLAP one and a new Tabular mode one.   Marco Russo has a brief but useful explanation of this in his blog post here.

As Marco says, the new Tabular mode engine is going to make great use of Data Analysis Expressions or DAX.  Learning DAX will be something all BI analysts and developers working with the Microsoft set of tools will want to do and his PowerPivot book is a good place to start.  

So you may ask, why am I reviewing a book on MDX, which is the multi-dimensional expression language used with the older multidimensional model?  Well, as exciting as all of the upcoming technology is, I think MDX is going to be around for a good while yet.  We don’t yet have a release date for Denali and many of the new features are going to need a lot of planning, particularly if you want to use the new reporting features such as Crescent along with SharePoint.  In the meantime, there is an opportunity to make use of the current SQL Server release to build very effective BI solutions with Analysis Services which will still work well once Denali is available. 

The book’s author, Tomislav Piasevoli is a SQL Server Analysis Services MVP based in Croatia.   He has a lot of real-world experience with Analysis Services, cube development and MDX and that certainly comes through in this book.  All of the cookbook “recipes” are well documented with a full set of downloadable examples.  And the technical reviewers for the book are among the most experienced in the world, including Marco Russo, Chris Webb and Greg Galloway.  This certainly shows through in the sample code – I’ve tried out a good few of these already and all work as described which unfortunately isn’t the case with some other books out there.  All examples use the good old AdventureWorks database samples in their 2008 R2 guise.  So its possible to work through everything in the book by installing the trial version of SQL Server, available as a free download, or maybe purchasing the developer edition which has all the features of SQL Server Enterprise.

As the title suggests, this is a “cookbook” covering lots of MDX solutions or “recipes”.  Tomislav’s technique is to lay these out as in a standard format: Getting ready, which sets up the initial query for the data you will use; How to do it which shows you a worked example with MDX code; How it works, describing what the code is doing and finally There’s more which will point you to other examples of how the technique might be used.

Each chapter of the book covers a specific set of problems or challenges which you might encounter when developing a multi-dimensional Business Intelligence solution and provides appropriate solutions.   You can take a look at the areas covered in the table of contents over at PackPub.  I haven’t tried out everything covered in the book – in the same way as it’s unlikely you would cook every dish in a real cookbook unless you are Julie Powell and you want a book/film deal.  Here are some of the topics I’ve been impressed with so far –

Working with Time – lots of examples on adding time intelligence to your cubes and MDX queries along with several examples of calculating today’s date.  As Tomislav says, this is one of the most frequently asked questions in online searches and forums.

Concise Reporting – How to use a set to make your analytical queries more compact and concise.  Examples here cover topics like isolating best or worst members, displaying important members plus “others” and “totals” rows and finding exceptions.

This isn’t a book for anyone completely new to MDX but is definitely an excellent resource if you have already started to work with it want good real-world advice.   MDX isn’t an easy language to master and I think regardless of your experience level you will find something of value here.   Well worth buying.   And if you are completely new to MDX I would suggest starting with the Stairway to MDX series on SQLServerCentral.com (registration required).

If you are a Kindle user the book is available from Amazon and you can download a sample from there to get a better feel for how the cookbook and its recipes are structured.

Technorati Tags: ,,

Posted in Business Intelligence, SQL Server | Comments Off on Book Review- MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook