Yet another interesting situation where we had to display an Report on Excel and put it on SharePoint Power pivot gallery. We selected the Cube database and dragged in a few dimensions and Measures along with a few slicers. The query that was auto generated was not very optimized.
The report was to show data only for last one week. One of the slicer "Week Starting Date" was to have dates for the last one month only.
We had to use some hidden filters to ensure that we show only part of the data. i.e. only last 4 weeks data. There are few ways (Slicer Settings) where we can visually indicate that Items that do not have data should be disabled (will be shown) and also there is a way to move those items to the end of the slicer. There is no way of removing that altogether.
In such situations we can write a custom query to only fetch data with Week Starting Dates that is within the last one month range. This also improves the performance because of the optimized query against the Cubes.
Excel do not provide a direct way to update the query. There are a few Adins/products that aid to this, like Vivid http://www.varigence.com/products/vivid/features/navigation that will come into handy.
Another approach could be to use an ODC File where you can embed the MDX query. I am attaching a sample ODC file. Pay close attention to the highlighted section.
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Cube>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=CubeDB>
<meta name=Table content=CDW>
<title>sqlserver2008r2 CubeDB CDW</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>sqlserver2008r2 CubeDB CDW</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSOLAP.4;Password=password;Persist Security Info=True;Data Source=Server\sqlserver2008r2;Initial Catalog=CubeDB</odc:ConnectionString>
<odc:CommandType>MDX</odc:CommandType>
<odc:CommandText>SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
</html>
Showing posts with label Excel Service Application. Show all posts
Showing posts with label Excel Service Application. Show all posts
Thursday, February 17, 2011
Wednesday, October 13, 2010
Troubleshoot on Setup of PowerPivot for SharePoint
I know it can be really frustrating when you spend long hours just trying to setup something on your developer box. Especially, when you had followed the instructions exactly (maybe not) as in the MSDN article.
The following MSDN article guides you to setup PowerPivot for SharePoint.
I will try to highlight some of the gotchas that you need to watch out for, while you perform the setup.
The following were the configuration that I have on my dev box.
- Domain Controller with Active Directory
- Windows Server 2008 R2
- SharePoint 2010
- SQL Server 2008 R2 Dev edition (With SSIS, SSAS & SSRS)
- Office 2010
- .Net 3.5 SP1 or above
- PowerPivot Add-in for Excel 2010 (http://www.powerpivot.com/download.aspx)
While following the Setup article, you may find some weird error like.
Failed to load receiver assembly "Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" for feature "PowerPivot" (ID: f8c51e81-0b46-4535-a3d5-244f63e1cab9).: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
If you see the above error then you will be really interested in the following detail of important steps.
- In the Installation Type step of the wizard, select “New installation or add shared features”. DO NOT select the “Add features to an existing instance…” option.
- In the Setup Role step in the wizard, select the “SQL Server PowerPivot for SharePoint” radio button. DO NOT select the SQL Server Feature Installation option.In the Instance configuration step, keep the Instance ID as POWERPIVOT. Keep the SQL Server Analysis Services of default MSSQLServer seperate from this instance required for PowerPivot
- In the Service configuration step, provide a domain account that has rights on both the SQL instances. I know it is not a good practice, but what the hell, this is supposed to be a developer box. I used the domain admin :)
- In the Read to Install step, you will have to browser to the configuration file path shown at the bottom of the dialog. Modify the FARMADMINPORT value to point to the existing SharePoint Central Administrator port.
- Before you move to the next step in the wizard,
- Ensure that you have the Microsoft.AnalysisServices.SharePoint.Integration.dll in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. This dll can be found in GAC from one of your previous failed attempts.
- Remove all instances of the Microsoft.AnalysisServices.SharePoint.Integration.dll, especially from the GAC. Find other instances of it (temp folders of GAC) and delete them as well.
- Note: If you are not able to delete some file instance because some process is holding it up then find out the process and go to the Task manager or the Windows Service to terminate/stop the process. Usually it will be w3wp.exe, OWSTimer, Web Analytics services. You can use the Process explorer to identify the holding process. http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
- Good Luck here.. before you press that Install button.
Have patience in the Last step. it could take a while to finish the last bit. I had to start the SharePoint timer service thinking, maybe the FarmDeployment workflow was not getting completed because of a timer service was terminated in the step 5 (I am not sure about this. Just in case you face some problems).
Post the installation, for configuration you can follow the MSDN article.
Just before you leave one Tip,
Once you install PowerPivot Add-in to Excel you may see that loading of Excel becomes slow. If you may wish to enable/disable this add-in then go to Excel -> File -> Options -> Add-ins -> Manage (COM/Disabled Add-ins)
Hopefully, this information has saved you that precious time.
Now, that I have been able to setup, I am waiting to explore PowerPivot for SharePoint in detail. If I come across anything interesting then I will share my views in the next article. Till then stay tuned :)
Just before you leave one Tip,
Once you install PowerPivot Add-in to Excel you may see that loading of Excel becomes slow. If you may wish to enable/disable this add-in then go to Excel -> File -> Options -> Add-ins -> Manage (COM/Disabled Add-ins)
Hopefully, this information has saved you that precious time.
Now, that I have been able to setup, I am waiting to explore PowerPivot for SharePoint in detail. If I come across anything interesting then I will share my views in the next article. Till then stay tuned :)
Subscribe to:
Posts (Atom)