Setting Up a SharePoint 2013 BI Demo VM

December 8, 2012

I just built up a new demo environment for BI with Windows Server 2012, SQL 2012 SP1 and SharePoint 2013. This is primarily to take advantage of the new Power View enhancements that came out with SQL 2012 SP1 but I do step through getting all the other BI services up and running. What follows is a general guide, providing an overview of the main steps and links to other information where necessary. I am focusing on the SQL and SharePoint install here, so I assume you have a Windows Server installation ready and have setup a domain and DNS services. I used Windows Server 2012 for my VM.

Pre-requisites

  1. If you don’t have an internet connection from your VM, make sure you have downloaded the Sharepoint 2013 pre-requisites. An in-depth guide exists on TechNet which discusses options on obtaining the pre-requisites, and then this guide will help you install them quickly.
  2. Then you will also need the following

Installing SQL Server 2012

  1. Ensure Windows has the Active Directory Domain Services role instealled. This is important so we can use a proper domain account for services. Ive seen problems when using built in service accounts. It will suggest you add in the DNS Server role a well. I did so. In my virtual machine I setup 2 network adapters – one with a fixed IP on an internal virtual LAN Segment, the other bridged so it could access my host’s internet connection.
  2. Install a DEFAULT instance of SQL Server 2012. I used slipstreamed media which has SP1 built in. Things to note:
  • I used the domain Administrator account to run all services except for the Browser, Agent and Full Text Filter.
  • Remember to add the domain Administrator account as an admin to the relevant services as you configure them in the install Wizard. You don’t want to be locked out later

These are the features I selected (pretty much all)..

… Noting that when I configured the services, I picked Multidimensional and Data Mining Mode:

  1. I then ran the Reporting Services Configuration tool to setup the Native SSRS instance.
  2. I wanted a Tabular version of SSAS, so I ran the installer again and chose to install a NAMED instance of SQL Server 2012, which I called TABULAR. This time the only feature I selected was Analysis Services, but when asked to choose the mode I selected Tabular.

SQL Server 2012 Help

For some reason Microsoft decided not to include local help files on the SQL install media so you need to get it yourself and manually install it. It can be a bit tricky so pay attention to the instructions that follow the download:

Developer Tools

At this point I would recommend installing the following:

  • SQL Server Data tools (i.e. SSDT – highly recommended)
  • SSDT Power Tools (the official mechanism to update SSDT)
  • Visual Studio 2010 with SP1 (if you want need complete .Net programmability)

Installing Sharepoint 2013

  1. Start the SharePoint 2013 installer (autorun from the dvd/mounted ISO, or manually run default.hta). If you havent already installed the pre-requisites then do so using the option shown below:

  2. After pre-requisites are installed, from the install launcher shown above select the “Install SharePoint Server” option. When the SharePoint install has completed, De-select the option to run the configuration wizard.

Installing PowerPivot for SharePoint

  1. You will need to get back to the SQL Server 2012 installation media and run the SQL installer. Step through the usual installer screens and select the PowerPivot option when you are asked to choose the Role as shown below. Be sure to de-select the option to install the relational database (we will use the default instance we set up earlier to host SharePoint databases)

  1. At the Instance Configuration screen of the wizard i entered POWERPIVOT for my Instance ID

  2. Step through the remainder of the installer, remembering to add yourself as an administrator when prompted. I also chose to run the service using the Administrator account.

Base setup of up the Sharepoint Farm

Instead of using the SharePoint Products Configuration Wizard to configure our farm, we use the new PowerPivot Configuration tool to do this for us. The beauty of it is it will setup a bare minimum Sharepoint farm. Nice and lean, so it will run quicker which is important for demos. I find the Microsoft supplied VMs HUGE (75Gb) and slow. Check here for my guide on how to convert one from Hyper-V to VMWare. Just a reminder that Windows 8 includes client Hyper-V which is great if you want to stay away from VMWare products for any reason.

  1. Start the PowerPivot Configuration Tool from the SQL Server 2012 Program Group:

  1. Pick the first option as shown – there will be no choice on an unconfigured farm anyway

  2. The tool will perform some checks and then present you with a list of tasks. Fill in the first screen with settings to match your setup, then click Validate. You’ll see this:

    At this point you could press Run and have it perform all those activities. However, whats great about this setup compared to the SQL 2008 R2 is that we can actually customise some of Service Application and associated database names to remove the dreaded GUIDs that get appended to them. You can also control some of the IIS setting such as Web Application name and App Pool. These are the tasks I suggest click on to customise:s

  • Create PowerPivot Service Application
  • Create Default Web Application
  • Deploy Web Application
  • Create Site Collection
  • Create Unattended Account for DataRefresh
  1. When you’ve finished customising you’ll notice it informs you where you’ve made changes to the tasks. You can click the Script tab to view the PowerShell commands that will be executed. This is great to learn from and also to save away for a future scripted setup. I can see this being useful to tweak for a proper production setup. Beware: you need to select the topmost task to get the whole script showing up. This means if you are looking to find the PowerShell commands for just a select task/task group, you can click the appropriate section.

  2. Click Validate, and if successful, click Run. This will take a bit of time… If all goes well you will see this:

    And you can verify in IIS that the sites were setup:

  3. Navigate to our new SharePoint site. I usually at this point remember to turn off Internet Explorer Enhanced Security by going to Server Manager and selecting the appropriate option under Local Server

  4. Finally I add the Sharepoint Site to the trusted sites in Internet Explorer.
  5. So now our site should work and if you browse to it you should see the vanilla Sharepoint page configured with a PowerPivot Gallery.

    There are two more components left to configure for a basic BI setup. SSRS/Power View and then PerformancePoint.

Enabling SharePoint Enterprise Features and Services

Before we try to setup these other BI services it’s a good idea to turn on other services we might need for demos and also Enterprise features for the Site Collection and Site.

  1. First in Central Admin select Manage Services on Server:

In there I enabled:

  • Business Data Connectivity Service
  • PerformancePoint Service
  • Visio Graphics Service
  1. Then in SharePoint go to Site Settings which is now accessed from a little “gear” icon in the top right corner of the browser window:

  1. Then manage the Site Collection Features:

Make sure the following are activated (Many will already be active, but ive stated all for completeness):

  • PerformancePont Services Site Collection Features
  • Power View Integration Feature
  • PowerPivot Feature Integration for Site Collections
  • SharePoint Server Enterprise Site Collection features
  • SharePoint Server Publishing Infrastructure
  1. Next we have to set enable some features at the Site level. So go back to Site Settings as in Step 1 above. This time select Manage Site Features:

    Make sure the following are active:

  • BICenter Data Connections Feature
  • Mobile Browser View (if you plan to test mobile features)
  • PerformancePont Services Site Features
  • SharePoint Server Enterprise Site features
  • SharePoint Server Publishing

Note we don’t turn on the Report Server File Sync because that is only required for SSRS 2008 R2 to synch back to the SSRS catalogue. In SQL 2012 SSRS is now a proper Sharepoint Service Application – something that was done to address performance problems in Sharepoint integrated mode.

Configuring SSRS Integration and Power View

This part tripped me up the first time. I thought I had everything working but SSRS and reports simply wouldn’t work! After some research, trial and error I understood all the components and config required to get it to work. The main problem is that with the steps we’ve taken so far, the SSRS Service itself (new in SQL 2012) and its proxy are not actually setup in Sharepoint yet.

A couple of quick PowerShell commands I found on the MSDN SSRS Integration Configuration Guide helped here, in the section entitled “Install and Start the Reporting Services SharePoint Service”. The note there explains why we don’t have SSRS services configured – we installed SQL first so there was no farm for it to configure SSRS on.

  1. Run the Sharepoint PowerShell as an admin:

  1. Run some PowerShell commands to install the service installed, add the proxy and then start the service:
    1. Install-SPRSService
    2. Install-SPRSServiceProxy
      
    3. get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
      
  2. Next we can create a SSRS Service Application from Central Admin

    Heres where we select the one we want:

    The settings ive used are highlighted (first page):

    Then scrolling down:

    This should get you to the Success screen shown below – Click the highlighted link to ProvisionSubscriptions and Alerts.

    This will take us to the page below where the SSRS service account is given access the SQL Agent. Note the credentials below are sent to the SQL server.. so using the “sa” account will work here too. Note that you can create subscription and alerts if the SQL Agent service is not running – so turn it on if you want to demo this feature.

  3. How to test this? Make sure you have Silverlight installed and then upload a PowerPivot workbook into the PowerPivot Gallery. The Gallery has a link automatically if Silverlight isnt present. You will see a new “Create Power View Report” option in the gallery:

Clicking that will open up the Power View designer:

Configuring PerformancePoint Services

This part is much easier!

  1. Verify the PerformancePoint Service is running. In Central Admin you need to go to “Manage Services on Server” (shown below) , and once in there just Start the PerformancePoint Service if it isn’t running.

  2. Next we need to add the PerformancePoint Service Application. So in Central Admin once again we go to the Manage Service Applications page as we did with the SSRS service.

  1. Configure the service similar to how we did SSRS, shown below:

and continuing:

If all goes well you should see this:

  1. Next we need to configure the Unattended Service Account – its not absolutely necessary but a good idea to get done now. It all depends on how you plan to access your data sources – Unattended gives you the highest level of caching in PPS and the data source. So go back Manage Service Applications in Central Admin, and click on the newly provisioned service:

Once there, you need to change the app settings:

  1. We only need to set the account, the other settings on the page can be left at their defaults

  1. Its worth noting that there is a new option available in SharePoint 2013 for PPS which is to use the EffectiveUserName property in the connection string to SSAS:

Client Tools

Since we haven’t used the Business Intelligence Centre site template there will be no shortcut button to get to Dashboard designer. This can be reached via the following URL, which you can add to the site menu, a page or even customise via Sharepoint Designer:

http://<siteURL >/_layouts/ppswebparts/designerredirect.aspx

Just a few things left now to make this complete. You will need to install:

  1. Excel 2013 to demonstrate the now built PowerPivot and Power View
  2. Excel 2010 and then the items below (I have not found any addins compatible with Excel 2013 yet!)
    1. Data Mining Addins
    2. Master Data Services Addin
  3. Visio 2013
  4. PowerPoint 2013 (to show export capability from Power View)

Samples

Now install the SQL Server samples if you need some data to play around with.


Setting Up a Custom SQL2012 and Sharepoint 2010 BI machine

April 23, 2012

A few days ago I finally got my custom Sharepoint 2010 and SQL 2012 demo image ready. With only a few days to go till my PowerPivot and Power View presentation at SQL Saturday 138 in Sydney, this was very relieving!

There are a few changes to how you would do this compared to the SQL 2008 R2 setup. Hence I thought I would share my experiences as I went through some pains to get it all right. Note here I am setting up a single machine for demo/evaluation purposes only. So, I will not be following the general best practice recommendation of using separate service accounts and Application Pools.

My aim was also to build up a “lean” machine that has the minimal Sharepoint services required to run BI demos. Hence, the steps I follow are probably not what you would do for a production environment setup.

I also acknowledge that scripting a lot of this in PowerShell can save a lot of time. However, I believe you have to go through it at least once to understand the components and what to script in the first place J This is not a detailed step by step post for every item – links are provided at times. However for the BI specific configuration, especially what has changed, I’ve been quite specific.

Whats New in this setup?

Ok, the main differences in this install/setup compared to SQL 2008 R2 are:

  1. SSIS is now a true service with its own Catalog
  2. Data Quality Services (DQS) has been introduced
  3. Master Data Services (MDS) has been revamped a lot
  4. SSRS is now a proper Service Application in Sharepoint and hence its configuration is different. SSRS now also includes PowerView
  5. You will need 3 instances of SQL Analysis Services
    1. Multidimensional and Data Mining (i.e. what we have had since SQL 2005)
    2. Tabular
    3. PowerPivot Integrated
  6. There is now a PowerPivot configuration tool to assist with setting up the PowerPivot integration. This is greatly improved from the previous PowerPivot setup experience, where it was either all completely manual on an existing farm. The other option in SQL 2008 R2 was where you could use the PowerPivot for Sharepoint Setup option from the SQL installer on an unconfigured farm. This would configure the farm for you. This had a couple of drawbacks in my opinion:
    1. It would install an own named instance of the SQL Server called POWERPIVOT, containing the Sharepoint databases
    2. Unless you setup using PowerShell, you could not control the naming of the Service Application Databases, and those horrible GUIDs where always suffixed. Below is a reminder from my 2008 R2 VM. While we cant get rid of all of the GUIDs, we can for most. Again Powershell is the solution if you want complete control.

I have not yet configured MDS and DQS and I may blog this in another set of posts. From memory when I played with it in Denali RC0 DQS was fine but I had issues with MDS and its Silverlight 5 requirement, which was still beta.

Pre-requisites

  1. If you don’t have an internet connection from your VM, make sure you have downloaded the Sharepoint 2010 pre-requisites. A handy PowerShell script exists on TechNet to save you manually doing all this.
  2. Then you will also need the following

Installing SQL Server 2012

  1. Setup Windows Server 2008 R2 and add the Active Directory Domain Services role. This is important so we can use a proper domain account for services. Ive seen problems when using built in service accounts. It will suggest you add in the DNS Server role a well. I did so. In my virtual machine I setup 2 network adapters – one with a fixed IP on an internal virtual LAN Segment, the other bridged so it could access my host’s internet connection.
  2. Install a DEFAULT instance of SQL Server 2012. Things to note:
  • I used the domain Administrator account to run all services except for the Browser, Agent and Full Text Filter.
  • Remember to add the Administrator account as ad admin to the relevant services as you configure them in the install Wizard. You don’t want to be locked out later

These are the features I selected (pretty much all).. noting that when I configured the services, I picked SSAS Multidimensional.

  1. I then ran the Reporting Services Configuration tool to setup the Native SSRS instance.
  2. I wanted a Tabular version of SSAS, so I ran the installer again and chose to install a NAMED instance of SQL Server 2012, which called TABULAR. This time the only feature I selected was Analysis Services, but when asked to choose the mode I selected Tabular.

Installing Sharepoint

  1. This part is simple. I just ran the installer, went through the pre-requisites install and then installed Sharepoint. When the Sharepoint Installer asks you what type of install don’t choose Standalone. Use the Farm option. The standalone version will go ahead and install SQL express which is not what we want.

  2. Once Sharepoint is installed don’t run the Configuration Wizard
  3. Install SP1 (if you need to i.e. your install source didn’t have it built-in), and then the latest Cumulative Update

Base setup of up the Sharepoint Farm

This is where things get interesting. We are going to use the new PowerPivot Configuration tool to do this for us. The beauty of it is it will setup a bare minimum Sharepoint farm. Nice and lean, so it will run quicker which is important for demos. I find the Microsoft supplied VMs HUGE (75Gb) and slow. Check here for my guide on how to convert one from Hyper-V to VMWare.

  1. Start the PowerPivot Configuration Tool from the SQL Server 2012 Program Group

  2. Pick the first option as shown – there will be no choice on an unconfigured farm anyway

  3. The tool will perform some checks and then present you with a list of tasks. Fill in the first screen with settings to match your setup, then click Validate. You’ll see this:

    At this point you could press Run and have it perform all those activities. However, whats great about this setup compared to the SQL 2008 R2 is that we can actually customise some of Service Application and associated database names to remove the dreaded GUIDs that get appended to them. You can also control some of the IIS setting such as Web Application name and App Pool. These are the tasks I suggest click on to customise:

  • Create PowerPivot Service Application
  • Create Default Web Application
  • Deploy Web Application
  • Create Site Collection
  • Create Unattended Account for DataRefresh
  1. When you’ve finished customising you’ll notice it informs you where you’ve made changes to the tasks. You can click the Script tab to view the PowerShell commands that will be executed. This is great to learn from and also to save away for a future scripted setup. I can see this being useful to tweak for a proper production setup. Beware: you need to select the topmost task to get the whole script showing up. This means if you are looking to find the PowerShell commands for just a select task/task group, you can click the appropriate section.

  2. Click Validate, and if successful, click Run. This will take a bit of time… If all goes well you will see this:

    And you can verify in IIS that the sites were setup:

  3. Navigate to our new SharePoint site. I usually at this point remember to turn off Internet Explorer Enhanced Security, and I add the Sharepoint Site to the trusted sites. Makes things easier!
  4. So now our site should work and if you browse to it you should see the vanilla Sharepoint page configured with a PowerPivot Gallery. I wont include a screenshot. There are two more components left to configure for a basic BI setup. SSRS/Power View and then PeformancePoint.

Enabling SharePoint Enterprise Features and Services

Before we try to setup these other BI services it’s a good idea to turn on the Enterprise features for the Site Collection and Site.

  1. In SharePoint go to Site Settings:

  1. Then manage the Site Collection Features:

Make sure the following are activated (Many will already be active, but ive stated all for completeness):

  • PerformancePont Services Site Collection Features
  • Power View Integration Feature
  • PowerPivot Feature Integration for Site Collections
  • SharePoint Server Enterprise Site Collection features
  1. Next we have to set enable some features at the Site level. So go back to Site Settings as in Step 1 above. This time select Manage Site Features:

    Make sure the following are active:

  • PerformancePont Services Site Features
  • SharePoint Server Enterprise Site features
  • SharePoint Server Publishing

Note we don’t turn on the Report Server File Sync because that is only required for SSRS 2008 R2 to synch back to the SSRS catalogue. In SQL 2012 SSRS is now a proper Sharepoint Service Application – something that was done to address performance problems in Sharepoint integrated mode.

Configuring SSRS Integration and Power View

This part tripped me up the first time. I thought I had everything working but SSRS and reports simply wouldn’t work! After some research, trial and error I understood all the components and config required to get it to work. The main problem is that with the steps we’ve taken so far, the SSRS Service itself (new in SQL 2012) and its proxy are not actually setup in Sharepoint yet.

A couple of quick PowerShell commands I found on the MSDN SSRS Integration Configuration Guide helped here, in the section entitled “Install and Start the Reporting Services SharePoint Service”. The note there explains why we don’t have SSRS services configured – we installed SQL first so there was no farm for it to configure SSRS on.

  1. Run the Sharepoint PowerShell as an admin:

  1. Run some PowerShell commands to install the service installed, add the proxy and then start the service:
    1. Install-SPRSService
    2. Install-SPRSServiceProxy 
    3. get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance 
  2. Next we can create a SSRS Service Application from Central Admin

    Heres where we select the one we want:

    The settings ive used are highlighted (first page):

    Then scrolling down:

    This should get you to the Success screen shown below – Click the highlighted link.

    This will take us to the page below where the SSRS service account is given access the SQL Agent. Note the credentials below are sent to the SQL server.. so using the “sa” account will work here too. Note that you can create subscription and alerts if the SQL Agent service is not running – so turn it on if you want to demo this feature.

  3. How to test this? Make sure you have Silverlight installed and then upload a PowerPivot workbook into the PowerPivot Gallery. The Gallery has a link automatically if Silverlight isnt present. You will see a new “Create a PowerPivot Report” option in the gallery:

Clicking that will open up the Power View designer:

Configuring PerformancePoint Services

This part is much easier!

  1. Verify the PerformancePoint Service is running. In Central Admin you need to go to “Manage Services on Server” (shown below) , and once in there just Start the PerformancePoint Service if it isn’t running.

  2. Next we need to add the PerformancePoint Service Application. So in Central Admin once again we go to the Manage Service Applications page as we did with the SSRS service.

  1. Configure the service similar to how we did SSRS, shown below:

and continuing:

If all goes well you should see this:

  1. Next we need to configure the Unattended Service Account – its not absolutely necessary but a good idea to get done now. It all depends on how you plan to access your data sources – Unattended gives you the highest level of caching in PPS and the data source. So go back Manage Service Applications in Central Admin, and click on the newly provisioned service:

Once there, you need to change the app settings:

  1. We only need to set the account, the other settings on the page can be left at their defaults

Client Tools

Since we haven’t used the Business Intelligence Centre site template there will be no shortcut button to get to Dashboard designer. This can be reached via the following URL, which you can add to the site menu, a page or even customise via Sharepoint Designer:

http://<siteURL >/_layouts/ppswebparts/designerredirect.aspx

Just a few things left now to make this complete. You will need to install:

  1. Excel 2010
    1. PowerPivot 2012 RTM
    2. Data Mining Addins
  2. Visio 2010
  3. PowerPoint 2010 (to show export capability from Power View)
  4. You need to install the Desktop Experience feature on the server in order for the Publish to Sharepoint feature of Excel 2010 to work. If you don’t, you wont be able to browse the Sharepoint site from Excel at all.

Converting the Microsoft Integrated SQL 2012 Demo from Hyper-V to VMWare Workstation

April 14, 2012

Today I decided to get to a long overdue task – looking at Microsoft latest all up BI demo VM containing SharePoint 2010 and SQL 2012 RC0 which includes Power View. Now we know SQL 2012 went GA last week, but this demo was released a couple of months ago so has RC0 which was close to the RTM version anyway. On mssalesdemos.com you will find the download, its called “New SQL Server 2012-RC0 fully integrated Demo”.

Before you download from there beware that the download is split into many rar files and if you prefer a single download I suggest you go here instead. Be warned, the file is about 28.4Gb, so you will need a stable internet connection and are advised to use software that can resume downloads just in case the connection breaks. This will extract to about 65Gb, so have enough disk space on hand. You will need even more to get to the final result we are looking for.

Now, one problem you may face is that the demo is a Hyper-V image, which means unless you have a Windows Server 2008 environment available, you cant run it. I use VMWare Workstation for all my VM work for the demos and presentations I do. So I needed a way to convert this. Warning: converting it will cause Windows and Office on the image to be non-activated. I used my own TechNet keys to re-activate after conversion.

There are many resources online telling you how to perform the conversion. However I found I had to hunt around and collate information from a few sources to get it right. There are also a few traps that are not so fun. So, my aim was to save others trouble and have it all in one place. Warning: Back up your extracted VM first before following any steps below!

There are 2 ways I found to convert the image over to VMWare, kindly blogged by Jim Holmes (Twitter: @aJimHolmes)

  1. Create a Windows Virtual PC and then convert using VMWare VCenter Converter
  2. Convert the .vhd disk to a VMware compatible .vmdk and then create a native VMWare image attaching that disk instead

Option 2 needs the Professional version of WinImage, so I opted to go with Option 1 instead, which I admit is longer, but a good learning experience and will save you having to buy WinImage or similar. If anyone knows of a free tool to do this a comment would be great!

Step 1 – Try to create a Virtual PC

So, following option 1, I tried to create the VPC using the downloaded VHD but I got an error.

Some research led me to discover that this is because the VHD is too big – the limit for Virtual PC appears to be 127Gb, but the disk size in the demo is 250Gb. I found this out by simply mounting in in Windows 7 use the Disk Management are of the Computer Management console

:

And here it is:

I’d like to clear up potential confusion here – the “SQL2012-RC0-ImageX.vhd” file is 75Gb, but it’s a Dynamic Disk so this represents its physical size whereas the 250Gb is its logical size. Dynamic Disks are useful as they don’t pre-allocate space beforehand. On the other hand performance is affected a little.

So I needed a way to shrink this disk.

 

Step 2 – Shrink the VHD so that it is not too big for Windows Virtual PC

This can be done easily using VHD Resizer. However the problem is you cant shrink this disk because the partitions within it are occupying the whole disk. So first we need to shrink the partition represented by drive letter J in the above diagram. Any partition management tool will do the job, but you are need to defragment it first so you can get to the smallest possible size. This is because you cant make partition smaller than the furthest bit of data stored on it. I used O&O Defrag to do both at once, and chose to shrink the partition to 90Gb to leave some free space (15Gb). Heres the result:

 

Once this was done I was able to use VHD Resizer to shrink the disk. I detached it form the Computer Management console first. Note in the image below I picked a separate drive as the destination copy on purpose. This will be significantly faster than creating the new vhd on the same physical drive on your PC. On my system I achieved about 90Mb/s which still meant waiting 15 minutes.

 

Step 3 – Create VPC, then convert to a VMWare workstation image

Now that the vhd is resized we can create the Virtual PC and it wont give us the error we got in Step 1. Windows virtual PC will create 2 files for the new VM. Firstly, a .vmcx file which sits by default in C:\Users\<your_windows_user_name>\Virtual Machines. However, when you specify the location of the VM during creation it actually also creates a .vmc file in that location and this is the file that we will work with.

We are going to use VMWare Vcenter Converter. Once installed follow this post to convert the Virtual PC image to a VMWare image. There is one problem though, you will get this error:

 

The comment at the end of the blog post guided me as to what was wrong, and it took me a while to find that post in the first place. There are a lot of irrelevant links on the Web for that Vcenter Converter error that can lead you on a wild goose chase. The fix for this is to open the .vmc file in a text editor (I use NotePad++) and change the <relative> node details to be a full physical path to the vhd.

Before:

 

After:


Now VCenter converter will have no problems reading the file and will happily convert to your chosen VMWare format, including converting the disk to a .vmdk.

I converted it to a VMWare workstation 7 image and it worked fine! That’s it!


Missing “Report Library” template in Sharepoint 2010

February 23, 2011

When working with the BI features of Sharepoint 2010 you may come across a situation where you configure the SSRS Integration and want to then add a Report Library. If you go to your Libraries and hit Create you get the template selection modal dialog (needs Silverlight installed.)

Here, under the Data category you would expect to see the “Report Library” template along with other BI templates. If you dont, it probably means that you dont have the Enterprise features enabled for the Site or Site Collection. Information on how to solve this issue is not forthcoming on the web. Here is what you need to do to get it to appear.

  1. Go to Central Administration
  2. Click Site Actions -> Site Settings:
  3. Under Site Collection Administration, select “Site Collection Features”
  4. Activate “Sharepoint Server Enterprise Site Collection Features”

This should fix your problem and enable the Report Library and other templates, which you can now add to the site. You may need to edit the library to allow the Report content types (effectively allowing the .rdl extension).


Corrupt SSAS databases – a partial solution

February 9, 2011

Today (for the second time in my expereince) i had a VM go corrupt and lost the normal operation of my SSAS 2008 R2 instance. I noticed that only one database was showing up in SSMS but it wasnt working i.e. you could not query/browse the cube due to error dialogs popping up.

Strangely other SSAS databases were queryable (i.e. dashboards worked) – these were the ones NOT showing up in SSMS.

I ran a SQL Profiler trace and noticed the following when trying to start the SSAS service:

Errors in the metadata manager. The dimension with ID of ‘Dim Customer’, Name of ‘Customer’ referenced by the ‘Customer Clusters ~MC’ cube, does not exist. Errors in the metadata manager. An error occurred when loading the Customer Clusters ~MC cube, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2.0.db\Customer Clusters ~MC.3.cub.xml’.

 

I looked at my data folder (as per error above) and it seemed like somehow a number of the XML files SSAS uses to stored definitions of objects were no longer named correctly.. the “~MC” didnt seem right.

Luckily for me it was the Adventureworks database that was corrupt. I needed access to the other databases which have my demo work on them. So i did the following:

  1. Stop SSAS service
  2. Browse to the Data folder
  3. Delete the following:
  • File: Adventure Works DW 2008R2.8.db.xml
  • Folder: Adventure Works DW 2008R2.0.db

This effectively removes the corrupt Adventureworks data files. Then i restarted SSAS service and was able to work as per usual.

So this is not the ideal outcome (i.e. cube lost) but it does allow your SSAS instance return to normal behaviour if one database is causing issues and you are unable to manage it with SSMS.


Incorrect format for date params (SSRS Integrated mode or using Report Viewer web part)

February 5, 2011

Yesterday I had a client ring me up saying some reports i deployed for testing were not functioning. The issue was that an input paramter of Date type was not accepting the date they chose – if they used the calendar picker. They got the error: “The vvalue provided for the report Parameter ‘Date’ is noty valid for its type.:

I am in Australia and we use the non-US (and more generally used) dd/mm/yyyy format in our dates. Closer inspection showed that after the users picked the date it was actually filling in the parameter using the incorrect (i.e. US) format: mm/dd/yyyy. We checked the locale settings in the report definition and the server itself and all was well: en-AU

The fix for this was found via google and another Aussie seems to have run across this problem

Essentially you need to edit the ReportViewer.aspx file and add the culture setting. The file is here by default:

C:\program files\microsoft SQL Server\MSQL.3\Reporting Services\ReportServer\Pages

At the top the page directive needs a change, as shown:

<%@ Page Language=”C#” AutoEventWireup=”true” Inherits=”Microsoft.ReportingServices.WebServer.ReportViewerPage” Culture=”en-AU”%>

Check out the comments on the link for some alternate solutions too.. it seems this fix did work for everyone.


A PowerPivot success story

February 4, 2011

Ive always been impressed with and realised the (forgive the pun) “power” of PowerPivot. However recently ive been charged with leading my organisation down a path of Microsoft BI across the enterprise – 1000+ employees.

So now ive really forced myself to join the PowerPivot revolution in a more hands-on fashion, seeing it as an ideal tool to give to our managers while we build the formal warehouse. ive used it to prototype chargables and utilisation tracking across our timesheets and im happy with the result so far. Now that ive had more experience with it and am much more comfortable with it i had agreat success story today at a client.

Background

The client called me in to advise on some best practices around Microsoft BI and help them address some issues around their attempt at building a cube. I also ran them through Sharepoint 2010/Excel Services/PerformancePoint (whic hthey LOVED)! But thats besides the point.

The current situation saw them loading masses of data straight from production servers into classic Excel pivot tables (e.g. 1gb+) and refreshing that daily to then carry around – more sales force oriented. They also have a number of power users who are very comfortable with Excel but were hampered by its classic pivot limitations. They also manage to bring down their prod DB every now and then. Not good for an ERP (or any system really)!

 

Prototype Solution:

So I fixed up their cube to make it work (though hit was far from best practice – based on as single denormalised table!) and then i pulled those rows into PowerPivot – which they had never seen before.

Asked them to take a look at the row count – 1.7 million

Asked them to save the file and take a look at the size – 6.65Mb! “How does it do that?”  was the question. Enter explanation of Vertipaq :)

Once they understood the flexibility of multiple data sources, custom measures, plus the compression factor they went nuts! Add to that the web based delivery via Sharepoint – sold! They now have a lot to play with and think about and to add to this they are much closer to making a decision to implement Sharepoint 2010. All this in 3 hours!

I think the best thing Microsoft did was make the PowerPivot add-on free. Now thats value :)


Follow

Get every new post delivered to your Inbox.

Join 300 other followers