How to create a Power View report using Sharepoint 2010 and Analysis Services 2012 Tabular as the data source?
0) Prerequisites
A server called "bisql01" with the following services installed:
- sql server 2012 (mssqlserver = default instance)
- analysis services 2012 (mssqlserver = default instance)
- analysis services 2012 tabular mode / vertipaq (astabular = named instance)
- reporting services (mssqlserver = default instance)
- sharepoint 2010 installed at https://powerview.locksnlatches.com
All services run under user "cloud\bi01_sql". Work is done locally on this server and refered to as "localhost". Data is loaded from the SQL Server database "CPDV" into the Analysis Services database "CPDM".
INSIDE VISUAL STUDIO 2012
1) create project
new project -> analysis services tabular project
name: cp_windows7
2) set deploy target of model
rightclick project "cp_windows7" -> properties ->
server: localhost\astabular
database: cpdm
impersonation settings: default
note: there is probably also a default instance of analysis services running; this is the regular multidimensional cube instance like the previous versions of sql server, not the named tabular model instance
note: the cpdm tabular database will be created on deploy
note: there will also be a work tabular database for the current project named after the project, first data source and a guid, e.g. CPDV_BI01_SQL_01e0810e-c8ab-46fd-afe6-098348336a9a. Do not delete this database from the analysis server server!
3) add tables to model
model -> import from datasource -> microsoft sql server:
server name: bisql01 (or "localhost")
log on to the server: use windows authentication
database: cpdv
use service account,
select tables/views,
finish
edit the bism model: model -> model view -> diagram view. If there are no relationships in the source, then they can be added here by dragging and dropping fields from a child to a parent table or vice versa.
change connection type: model -> existing connections -> edit
use sql server authentication
name: sa
password: ***
note: use model -> existing connections, to add tables later on from the same connection
4) set date dimension
view the data: model -> model view -> data view
click the date column, table -> date -> mark as date table
note: mark as date table does not work in the diagram view, only in the data view
note: make sure the date column is unique and continuous
note: a fact table can join to the date dimension on this date field, but also on a unique number field. A date field also needs to be selected in the latter case for "mark as date table" to work.
5) set authorization
models -> roles -> new
name: reader
permissions: read
models -> roles -> members -> add
cloud\bi01_sql
cloud\bi01_admin (=sharepoint account from which powerview is started)
note: if members are added to this role from management studio to the tabular model instance database then they will be overwritten at the next deploy of the project! So, specify the members in the project.
6) build -> deploy
INSIDE SHAREPOINT 2010
7) create semantic model connection
documents -> new document -> bi semantic model connection ->
filename: model.bism (this is the name of the document in sharepoint; it can be anything)
servername: bisql01\astabular
database: cpdm
note: to fix error "database cannot be found", double check that the reader role has read permissions and that the windows accounts used by Sharepoint are added to that group
8) create report data source to semantic model connection
documents -> new document -> report data source ->
data source type: microsoft bi semantic model for power vier
connection string: datasource='https://powerview.
locksnlatches.com/Shared%20Documents/model.bism'
credentials: stored credentials
username: cloud\bi01_sql
password: ***
availability: enable
9) create powerview report
click dropdown arrow of report data source -> create power view report
note: to fix error "User 'DOMAIN\user' does not have required permissions": start IE in "run as administrator" mode and connect using "localhost" iso. the server name.
note: the reporting services logfiles are located here C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\LogFiles
An OLE DB source to Oracle can be added to a SSIS package.
