SharePoint Search as a BI browser

This solution is based on a story happened during European SharePoint Conference this year. Andreas Knauer, an me are discussion about Search, Search Driven Application an Andreas had the idea using SharePoint Search as an BI browser. For me it was a great idea solving on of the biggest problems in information management also BI. The problem that users will not to use computer systems or learn how to use programs and tools – they will have answers!

So the big benefit here is that you did have to know exactly for what you search or what kind of options, characteristics or details exists – you can start with an high-level search term, the refiners show you then what options or characteristics are available for refining. User can use an enviremt that they already now – the search system known from internet search engines. With some customizing of the Results.aspx the resultpage shows the results as an excel-like view. Using the Profile Page Feature you can put some security in the solution, for example: the resultpage shows only high-level overview per item. To see the details you have to navigate to the Profile Page witch has a restricted access.

Of course this is not a BI browser solution fulfilling the needs from Controllers or you can use for deep analysis. Another problem is the data volume. Using this solution the content of you Data Warehouse will be part of the search index. Not really what you want.

So what can be a use case?! You can for example use such a solution to make you actual BI Data accessible for managers in you company only need a high-level view of the data and who didn’t have a deep technical knowledge in using BI Systems. If you limit the data so that only the actual year is indexed the data volume is practicable.  But you shut use a separate Search Service Application anyway for such a solution.

So now let’s see how you can do this.

NOTE: this solution is for illustration purposes only, not meant to be production ready!

Data source:

First we have to do is identifying the data we want to use. In this example I used the Microsoft demo Contoso_Retail_DW db.

If you have a special measure group in you Microsoft Data Warehouse based on Analysis Services you can take a look in the definition of this dimension. If it is bases on a T-SQL query you can use is direct in a view. If it based on an MDX Query you must access the Data via ODBC:

To separate the data I used T-SQL querys direct on the Contoso_Retail_DW db and create views based on it:

Data:

To limit the data range:

SELECT ProductKey, SUM(SalesQuantity) AS _SalesQuantity

FROM dbo.FactSales

WHERE(LoadDate > '2007-01-01 00:00:00.000') AND (LoadDate < '2007-12-31 00:00:00.000')

GROUP BY ProductKey

ORDER BY ProductKey

To define the datafields and details:

SELECT     p.ProductKey, p.ProductName, p.ProductDescription, p.BrandName, p.ClassName, p.ColorName, p.UnitPrice, psc.ProductSubcategoryName, pc.ProductCategoryDescription,

(SELECT _SalesQuantity

FROM dbo.V_ContosoRetailDW_FactSalesTop1000_SalesQuantitySUM AS fs

WHERE (ProductKey = p.ProductKey)) AS Expr1

FROM dbo.DimProduct AS p LEFT OUTER JOIN

dbo.DimProductSubcategory AS psc ON p.ProductSubcategoryKey = psc.ProductSubcategoryKey LEFT OUTER JOIN

dbo.DimProductCategory AS pc ON psc.ProductCategoryKey = pc.ProductCategoryKey LEFT OUTER JOIN

dbo.V_ContosoRetailDW_FactSalesTop1000_SalesQuantitySUM AS fs ON fs.ProductKey = p.ProductKey

I use the view based on the last query as input for an External Content Type in SharePoint. Index it with the SharePoint Search and create a Profile Page:

External Content Type:

Index it with the SharePoint Search:

Visualization:

With some XSL modifications and customized refiners the resulpage looks like this:

The next to last column is a calculated column, so you see it’s possible to bring some additional infos in…

The Profiles Page can be used to show details or to link to a high-end BI browser like PerformancePoint witch can be called with a parametric call

Customize search results from an external contend type with xslt and xpath

First let’s have a look at the underlying data first. In this case it’s a simple SQL database table. The table contains tasks from an ERP system:

Using SharePoint Designer we now integrate this table as an External List in SharePoint. Many other SharePoint bloggers have written or done presentations about this concept, for example: Ingo Karstein (Link)

Next step is now creating a profile page to show the search results from the external data in SharePoint Search. This is also a topic many other bloggers written about. Just see Corey Roth blog post: Link

Doing all this we get a search result looking like this:

….not really nice.

To Customize the Search Result using SharePoint Designer and XSLT follow the steps described by Laura Rogers (Link). If you do this with the external contend source you get something looking like this:

The point is there is no field like “Task” or “Date” as in the source SQL Table.

All this information is packed together in the field “hithighlightedsummary”. So first we have put this field to the table . You can do this via drag & drop. The result is similar like this:

Now click “Edit Formula….”in the context menu

You get something like this:

To filter special contend out of the “hithighlightedsummary” field just you Xpath. For example we want to filter out the “Task” field this Xpath expression will work (of cause only for my demo here): substring-before(substring-after(hithighlightedsummary, 'http://intranet/bcs'), 'miclas')

The result is showen in the Preview Section in the edit dialogbox:

With this Xpath query, some other sorting and design changes we are able to get a Data Source View looking like this:

And with the steps described by Laura Rogers (Link) we are now able to create a Search Result looking like this: