Showing posts with label Sharepoint 2010. Show all posts
Showing posts with label Sharepoint 2010. Show all posts

Power view on analysis services tabular


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