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