The WorkerThread Blog

we know stuff so you don't have to

Archive for the ‘Office’ Category

Excel 2013 Preview – Webservice Function and Flash Fill

Posted by workerthread on August 9, 2012

From everything I’ve seen so far Excel 2013 has some great new features for anyone interested in data analysis and Business Intelligence tools.   Both PowerPivot and Power View are included in this release (more on this later) and there are lots of new OLAP-specific features for anyone using Excel as a front end for multi-dimensional tools.

I saw this blog post from Chris Webb with an example of the new WEBSERVICE function.   As it happened I was recently looking at a file of locations for London bicycle hire stations – I thought it might be interesting to use this with some bike journey statistics against Excel 2013 Power View and maybe use the new mapping feature to show the busiest hire locations.   My file showed geographical location info as Easting and Northing and I thought I would be a good exercise to see if I could convert these to latitude/longitude columns for use in a Power View map.

Hunting around online, I found this article on converting easting/northing to latitude/longitude for an interactive map project.  The article uses a web service at to send an easting/northing pair and return the lat/long data in JSON format.   So will this work with Excel 2013 –


The answer is, yes it will, and I get JSON data returned which looks like this –

{"lat":"51.523344763137","lng":" -0.1838464075195"}

And of course I can copy the formula containing the WEBSERVICE function down the column –


Next I need to parse the JSON data so I end up with two new columns, one each for Latitude and Longitude.   Now it is of course possible to split strings of single-column data into separate columns using various combinations of MID, FIND and LEN functions and this would certainly work.  And there are various Excel VBA JSON parser examples around online.  But Excel 2013 has a new, interesting Flash Fill feature which you can read more about in this Excel Team blog post.   Giving Excel 2013 sample patterns to work with is the key here.  I wanted to try this with the data in my web service results column.

It’s worth noting a point made in step 3 of the Excel team post: “We decided to disable automatic Flash Fill for numeric data. After all, there are only 10 unique characters in our numbering system, and it’s just too easy to come up with random patterns that don’t make good sense”.   So to pull out the latitude values as numerical values I needed to to format the numerical precision on the column (in this case to 12 decimal points) and use the Data tab “Flash Fill” option –

And it worked!


My longitude values didn’t flash fill with numbers so successfully.   I think the problem here lay with the variable number of decimal places, so I got this message –


But the good news is if I entered the first example value with a label prefix, as soon as I start typing the second value, Flash fill recognises what I’m doing and suggests the fill I’m looking for –


UPDATE – if I settle for a lower precision and go for lowest common denominator of decimal places this does actually work with numbers.  So for example entering first 2 values with 8 digits of decimal value (such as –0.1838464) will allow me to flash fill with numeric data all the way down the column with no errors.

So overall pretty encouraging results.   Moving forward, if I were going to use the cleaned up web service results mashed up with other data in PowerPivot/Power View I would probably remove the web service function calls leaving behind just the cleaned-up data, thereby minimising the workbook refresh time.

One slightly disappointing finding, the WEBSERVICE function doesn’t currently seem to work in either SkyDrive or Office Web Apps in the Office 365 preview but maybe this will change by the time Excel 2013 is released.

Next up, a post on Excel 2013 PowerPivot and Power View.

Posted in Business Intelligence, Office | 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:


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 »

Review: Simego Data Synchronisation Studio

Posted by workerthread on May 31, 2011

I recently had the opportunity to take a look at Data Synchronisation Studio 3.0, an application developed and marketed by Simego Limited in the UK.   I was interested to see how it might be used to transfer data from or to SharePoint lists as I’ve noticed a fair amount of hits and comments on previous blog posts I’ve written which touch on this subject, like this one on using Integration Services to get SharePoint list data into a database.

Let’s consider first where Data Sync Studio (I’ll shorten the name from now on) is positioned, and how it might help with data synchronisation requirements.   If you are working with Microsoft SQL Server you will know that one of its very useful components is Integration Services,  an excellent tool for moving high volumes of data between applications.  If you are populating a data warehouse, loading database tables with data from an external source, cleaning, merging or de-duplicating data and you are a SQL Server customer, you need to get familiar with Integration Services.  But of course not everyone needing to move data around has access to Integration Services and even if they do, it may be the case that the data providers they need to work with aren’t catered for, either officially or unofficially.   I’ll look at two of those data providers here, namely SharePoint lists, and Active Directory Users, both available in Data Sync Studio.   You’ll see though at the bottom of this page there are many more, including the usual Microsoft database formats, Microsoft Exchange and CRM as well as Oracle, MySQL and OData.

Installing the Application

Data Sync Studio is a standalone .Net application which comes in 32-bit and 64-bit versions.   I’ve been trying it out on a Windows Server 2008 R2 virtual Machine, but you could just as easily install on a modern Windows desktop PC running Windows 7, Vista or even XP.   It is licensed per developer rather than per server.  Once you create a synchronisation project, if it is something which needs to be run on a scheduled basis then you will need to have a licenced copy of the application installed on the machine (desktop or server) which runs the scheduled job.

At the time of writing (May 2011) a single user licence of Data Synchronisation Studio currently costs 450 GBP / 720 USD.  More information on pricing and licensing options are shown on the product home page.

Importing or Exporting SharePoint Data

Data Sync Studio allows you to use a SharePoint list as either a data source or destination and you can synchronise data in both directions.  This means that you can fairly easily connect your list to a SQL Server table and keep the two in sync.   Of course you should first be sure this is the best approach to solving your business problem.  There are other methods to consider here, not least of all Business Connectivity Services, but BCS is definitely not the answer for everyone as it’s quite difficult to get right and again will need other tools to set up correctly – at the very least access to SharePoint Designer 2010.   Also I’ve often come across situations where data needs to be captured in SharePoint then later moved to an external database such as SQL Server for additional analysis, reporting or even archiving.

I took the example I used in my article on using Integration Services with SharePoint data.  I created the same list in SharePoint 2010 and a destination table in SQL Server 2008 R2.   In Data Sync Studio the SharePoint list became Data Source A and the SQL Server table Data Destination B:


First, I moved all of the current SharePoint list items to SQL Server.   This is easily accomplished using the “Compare A>B” button, which gives me a summary of the differences between source and destination.  I can then use the “Synchronise” button to push these changes to SQL Server.   Updates, additions and even deletions from the SharePoint list can also be handled and sync’d to the SQL Server destination:


And if you want, the sync can be bi-directional – you can specify which synchronisation options are used by setting properties in the source and destination:


Data Sync Studio also includes a “Dynamic Columns” feature which lets you create custom code to express columns.  These can range from fairly simple examples such as concatenating multiple columns (for example first and last name) or performing lookup or validation tasks.  One example provided is a “SharePoint Lookup Helper”.  You would use this if you were importing data into a SharePoint list and during import you needed to match a source value to the ID of an item in a separate lookup list.  Note that this facility is available via a C# programming interface so definitely requires .Net expertise.  You can read more about this here.

I also tried using the SharePoint data provider with Office 365 but couldn’t get this to work as it wouldn’t connect.  Simego tell me they are actively looking at this and I think it would be useful.   I can imagine for example a scenario where external users submit data to an Office 365 application and the data is sync’d with a corporate application or reporting system.

Working with Active Directory Data

Data Sync Studio includes LDAP Active Directory Users as a data provider which can be used to import this data into a database  or SharePoint list:


I tried this out, loading specific columns of the AD user list into a SharePoint list.   I could certainly see some uses for this.   Simego say they already have clients using this to populate a staff or team directory in SharePoint.   Also, although the Active Directory User data provider doesn’t allow inserts or deletes (a good thing) it does allow you to update configured columns.   This could be useful – it’s often the case that when an organisation implements SharePoint they want to get best value from searchable information in Active Directory, but fields related to job title, department, contact phone numbers etc aren’t populated.   This could provide a nominated user (maybe a member of the HR department) with a way of updating the fields in a list exported to SharePoint and then syncing the updated fields back to Active Directory.

It’s probably worth noting that bi-directional synchronisation is also possible with the SharePoint 2010 User Profile service, but again, this will present you with lots of configuration challenges and is definitely not for the faint-hearted.   Data Sync Studio may be a viable alternative here.

Creating a Project Schedule

Data Sync Studio lets you create a schedule for your synchronisation project using a dialog within the Studio itself:


Once you have selected dates and times to schedule a run and entered login credentials, a Windows scheduled task is created automatically for you.   A separate “Data Synchronisation Run Tool” is also provided within which you can schedule multiple projects to run in sequence and it’s also possible to execute a synchronisation project from the command line.

Summing Up

Data Synchronisation Studio is well worth a look if you have a need to move data between line of business applications and your requirement can’t be addressed by SQL Server Integration Services, either because you aren’t able or allowed use it or because the appropriate data providers aren’t available.   Certainly it won’t take the place of Integration Services when you are moving very high volumes of data to or from SQL Server and Simego don’t sell it as a replacement.   The fact that several of the data providers included currently aren’t in Integration Services could mean it pays its way quite quickly (as well as the two mentioned above I could also see it being valuable for Exchange data and for Microsoft CRM for example).   It’s also possible to create your own data providers and make these available as add-ins.   Simego provide some example add-in providers showing how this might be done, via a C# project.

You should be aware though that this isn’t an end-user tool.  It’s really aimed at developers with some coding experience, someone happier with developer tools such as Visual Studio, Integration Services or Business Intelligence Developer Studio.   Also, although the application comes with a help file and lots of online screenshots, it did take me a little while to understand the user interface and perhaps one or two additional “walk-through” sessions in the help file would be useful.   Having said that, you might also find what you are looking for in these blog posts.

Overall then my evaluation experience was a positive one.  It’s also good to see a home-grown (UK) development company coming up with a tool like this (and even spelling synchronisation without a “z”!).

Posted in development, Microsoft SharePoint, Office, SQL Server | Comments Off on Review: Simego Data Synchronisation Studio