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.
So now let’s see how you can do this.
NOTE: this solution is for illustration purposes only, not meant to be production ready!
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:
To limit the data range:
SELECT ProductKey, SUM(SalesQuantity) AS _SalesQuantity
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,
FROM dbo.V_ContosoRetailDW_FactSalesTop1000_SalesQuantitySUM AS fs
WHERE (ProductKey = p.ProductKey)) AS Expr1
FROM dbo.DimProduct AS p 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:
With some XSL modifications and customized refiners the resulpage looks like this: