Dienstag, 27. Dezember 2011

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:
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
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:
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

Samstag, 17. Dezember 2011

SQL Server 2012 File Tables feature and the benefit with SharePoint

SQL Server 2012 comes with an amazing new feature called “File Tables”.

That means that you can use a file system folder as a database table with all the fun and of cause nearly all the function of a database table. You have to enable the prerequisites for FileTable (as described here: LINK) and the result is a new table in your database with the following propertys:

The columns are fixed and base on the property coming from the NTFS file system. The columns are:
Column Name
Data Type
uniqueidentifier ROWGUIDCOL
varbinary(max) FILESTREAM

Each FileTable includes also these computed (read-only) columns:
Column Name
Data Type

You can query, update, insert or delete this table the same way as a “normal” table from you database with T-SQL. You also can use a fulltextindex and so on.
So in the context of SharePoint, especially with BCS you are now able to integrate file system data in you SharePoint without writing code and without growing up you SharePoint contend database with BLOB data. You can use file system data like documents, multimedia files or whatever as a SharePoint  External List, integrate it in you Search environment and using SharePoint Features like tagging, export to SharePoint Workspace and so on.

SQL 2012 File Tabel with SharePoint - for me a fantastic combination

Montag, 5. Dezember 2011

Donnerstag, 1. Dezember 2011

COUNT and SIZE of all docs in your SP2010 contend DB

There are a lot of use cases and scenarios you have to know the size and the count of the docs in you SharePoint content db. You can find it out in different ways, using the PowerShell , the Search (with a query like "%% -aspx -bdc3 -file") or 3th party tools. Another ways, and of cause a really smart one, is querying directly the SP contend db. The query if fired with a NOLOCK hint, so the load impact on your SharePoint system is really low.
The following scripts are written for SharePoint 2010:
--"count" and "size" of all docs in your SP2010 contend DB
--run script against your SharePoint 2010 wss_Content..... db
COUNT(*) AS 'Count',
(SUM(CONVERT(bigint,Size))/1024) AS 'Size in kbyte',
((SUM(Size)/1024)/COUNT(*)) AS 'AVG Size per Doc. in kbyte'
from dbo.AllDocs WITH(NOLOCK)
SELECT tp_DocID FROM dbo.AllUserData WITH(NOLOCK)--comment this clause out if you also want include the SP systemfiles
AND Extension != '000'

--"count" and "size" of all docs in your SP2010 contend DB group by fileextension
--run script against your SharePoint 2010 wss_Content..... db
COUNT(ID) AS 'Count',
(SUM(CONVERT(bigint,Size))/1024) AS 'Size in kbyte', --covert because of an "int" overflowrisk in a lager db
Extension AS 'Filetype',
((SUM(Size)/1024)/COUNT(*)) AS 'AVG Size per Doc. in kbyte'
from dbo.AllDocs WITH(NOLOCK)
SELECT tp_DocID FROM dbo.AllUserData WITH(NOLOCK) --comment this clause out if you also want include the SP systemfiles
AND Extension != '000'
ORDER BY 'Size in kbyte' DESC

Dienstag, 29. November 2011

Session stuff: Unleash the Power of SharePoint Search with Powershell

Here are the scripts from my PowerShell session at Collaboration Day 2011….:

#Unterschiede zwischen Search und FAST cmdlets:
get-help get-FASTSearchMetadataManagedProperty -examples
get-help get-SPEnterpriseSearchMetadataManagedProperty -examples

#Überblick verschaffen mit:
Get-SPSearchService | select *
Get-SPSearchServiceInstance | select *

#Ping the Search / FAST mit (Send a request to the default content distributor): 
Ping-SPEnterpriseSearchContentService -HostName "demo2010a.contoso.com"

#Übersicht über alle Search Applications:

#Properties mappen
Get-FASTSearchMetadataCrawledProperty -filter VorAngelegtVon
Get-FASTSearchMetadataManagedProperty -Name Author
#$vm = Get-FASTSearchMetadataManagedProperty -Name Author
#$vc = Get-FASTSearchMetadataCrawledProperty -filter VorAngelegtVon
#New-FASTSearchMetadataCrawledPropertyMapping -Managedproperty $vm crawledproperty $vc
Get-FASTSearchMetadataCrawledPropertyMapping  -name Author

#Calling KeywordQuery class
$searchSite = "http://intranet"
$site = New-Object Microsoft.SharePoint.SPSite $searchSite
$vc = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery $site
$vc.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch
$vc.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
#In my case I enabled the FQL syntax and set some other parameters:
$vc.EnableFQL = $true # enable FQL
$vc.RowLimit = 2 # sets the limit of results
$vc.StartRow = 0 # 0 is the default
#Next step is the query, in my case a simple query searching for the term “backup”
#Query / Result
$vc.QueryText = "backup"
$results = $vc.Execute()
write-host "Ergebiss:" -F blue
#Print out the results details:
#result details
$resultTable = $results.Item([Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults)
$rows = $resultTable.Table.Rows
#And getting the properties of each result:
#Each result with properties
write-host "Details:" -F blue
#This is just interesting and nice, especially getting all the property from the
#“HitHighlightedSummary” and the “HitHighlighted” Property fields.
#finding out with filetyps that are in the resultset and how they are distributed
$searchSite = "http://intranet"
$site = New-Object Microsoft.SharePoint.SPSite $searchSite
$vc = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery $site
$vc.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch
$vc.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
$vc.RowLimit = 1000
# Query String: the "%%" means to search everything, the "-aspx -bdc3 –file" filters to near only documents
$vc.QueryText = "%% -aspx -bdc3 -file"
$results = $vc.Execute()
$resultTable = $results.Item([Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults)
$rows = $resultTable.Table.Rows
$rows| Group-Object FileExtension| Sort-Object count -descending |Format-Table -AutoSize -Property Name,count
$rows| Measure-Object Size -Average -Sum -Maximum –Minimum

#Neues Rank Profil anlegen um PDF Dokumente höher zu gewichten:
$rp = Get-FASTSearchMetadataRankProfile -name default
$nrp = New-FASTSearchMetadataRankProfile -name pdf -template $rp
$fileext = Get-FASTSearchMetadataManagedProperty -Name fileextension
$nrp.CreateManagedPropertyBoostComponent($fileext, "pdf,-200000")
Remove-FASTSearchMetadataRankProfile -name pdf

#FullText Index
Get-FASTSearchMetadataFullTextIndex -Name content
Get-SPEnterpriseSearchQueryScope -SearchApplication "FastQuery"
#New-SPEnterpriseSearchQueryScope ..... -ExtendedSearchFilter "FullTextIndex=<FullTextIndex>".....
any further question etc.... just mail....