Putting it all together : MOSS 2007 - Business Data Catalog - Search - Excel Services - SQL Analysis Services
Would'n it be nice if you could use your search engine to retrieve ALL the information from a customer. When I say ALL, I mean ALL. (Info from your databases, content somewhere on your shares, content on the internet or (why not) content from your intranet solution. All the steps a user need to take are :
1) Enter the search criteria
2) Decide which link from the search results to follow
3) Take a look at the customer's dashboard page.
Now, the next question is : HOW DO WE BUILD A SOLUTION LIKE THIS.
In this example we will create a reseller dashboard that is reachable through your search results. The data comes in this example from SQL Server 2005 Analysis Services (because there are some special actions to take to enable this). Of course this scenario can be reused for other data entities regardless the data source (as long as it is reachable by Excel2007 & Excel Services)
Step 1 : Create your report in Excel 2007 & publish the result to your Microsoft Office Sharepoint Server 2007 environment.
Step 2 : Make sure that for each Reseller an entry in the Search Index is created.
Step 3 : Make sure Search Results are displayed correctly.
STEP 1 : Create your report in Excel 2007 & publish the result to your Microsoft Office Sharepoint Server 2007 environment
First of all : There are a copple of admin things to do in order to be able to publish Excel 2007 reports through Excel Services. How to do this is perfectly described on a Technet Resource I blogged about a few weeks ago. http://technet2.microsoft.com/Office/f/?en-us/library/7e6ce086-57b6-4ef2-8117-e725de18f2401033.mspx. This resource describes how to configure Single-Sign-On (If required), Data Connection libraries (connections strings that describe the physical connection with your data source) & of course the configuration of Excel Services (The component of MOSS that is responsible for the rendering of your Excel report within the MOSS 2007 environment). It is extremely important to understand how this works and I would advise you to take some time on this topic. It is powerfull, straight forward but take and hour or 2 to understand how this works - it will save you a lot of Grrmmmblllslsld afterwards). Webcasts available :
Once you've prepared your MOSS 2007 Environment you can publish your report. See http://blogs.msdn.com/excel/archive/2005/11/11/491970.aspx
Step 2 : Make sure that for each Reseller an entry in the Search Index is created.
This has to do with BDC (Business Data Catalog). A Good step-by step description on how to create a BDC application can be found http://www.sharepointblogs.com/ldusolier/archive/2006/09/22/12574.aspx. I used the http://www.bdcmetaman.com/default.aspx tool to create the XML file that configures th BDC engine to retrieve reseller information & publish this information to MOSS 2007.
The XML File looked like this :
TO BE COMPLETED !!!
Uploading this description can be done at the Central Admin pages :
After the application is uploaded you can review the data entities you've just created by clicking the 'View Entities" link. On the resulting page you can select the "Reseller" entity in our example. The next thing to do is to make sure that the URL linked with the "View Profile" Action is the URL of the page you want to go to when a user wants to see more information about this reseller. In our example the target page is
http://bi-vpc/Reports/ReportsLibrary/ResellerDashboard.aspx?ResellerKey=202&SSASResellerKey=[Reseller].[Reseller+Type].[Reseller].&[202] There are some issues with this URL :
-
As you can see there are some special characters like []& .
-
The [Reseller].[Reseller+Type].[Reseller].&[202] piece refers to an MDX statement that is necessary to pass to the Excel Pivot table parameter so that it can be used to filter information coming from SSAS & show only the information from the reseller hierarchie (limited to reseller 202).
-
Also notice the "&" sign which means that the next url parameter follows. but is this case it is part of the MDX Statement.
To make sure that the right URL is injected in the search index we will escape a number of characters. http://bi-vpc:80/Reports/ReportsLibrary/Reseller Dashboard.aspx?ResellerKey={0}&SSASResellerKey=%5bReseller%5d.%5bReseller+Type%5d.%5bReseller%5d.GUNTERSTAES%5b{0}%5d . Also notice "{0}" what is linked to the resellerkey field that is retrieved from the database by using BDC. Also notice that I put GUNTERSTAES instead of the "&" sign that is part of MDX statement. Of course if your data shown in Excel is not stored in SSAS you won't need this trick.
When all this if configured correctly we can configure the Search engine to crawl the content of our Reseller database on a regular basis & adjust the content of the search index.
Step 3 : Make sure Search Results are displayed correctly.
To make this happen we need to replace GUNTERSTAES in the url by %26 (Escape character for &) when we display the search result. This can be done by changing the XSLT that goes together with the web-part that shows the search results.
The code I ADDED in the XSLT is marked with yellow.
So, This is one of my largest postings. I hope you enjoy it. By the way : If you have suggestions on this please let me know.
