Reporting from Dataverse HLD options

Overview:  Recently I was looking at reporting from the Dataverse/CDS and I drew up these options that give the business various options but the overhead grew as the solution improves.  Client is on the MS stack so I have looked at the reporting options, There are a ton of variations but this is a good start for my options.

Reporting options from the Dataverse

Synapse, can be replace by any Data warehouse or Data lake solution and as Dataverse is not massive could also just use regular SQL Server for reporting.

Power BI can be replaced with other tools such as Tablau but for embedding and the MS stack, Power BI makes the most sense.

Power BI Notes

Overview:  Power BI is for reporting  and analytics of your data.  There are basically 2 ways to show Power BI Reports: User specific and app specific.

Power BI Embedding Models:

1. User Specific/User Owned Data - Call the Power BI services as yourself/the current user using delegate permissions. 
2. App Specific/App Owned Data - Call the Power BI service using a generic app permissions.  For example a public website, no Power BI licence required and every user of the site has the same access to view Power BI data.

Power BI online integrate into SharePoint on-prem. extranet Architecture

Power BI Embedded Online Licencing as of 22 Nov 2017:
Basically, there are 2 parts to licencing PowerBI online.
1.> You licence per the number of pages you render per hour.  You need to have the Power BI Embedded licences on infrastructure to serve up a certain number of requests per hour. So work out your peak number of page request per hour and licence for the appropriate plan.  The table below shows the Power BI Embedded plan you'll need to subscribe to:

Plan	Virtual CPUs	RAM (GB)	Max Request per hour
A1	1	3	300
A2	2	5	600
A3	4	10	1200
A4	8	25	2400
A5	16	50	4800
A6	32	100	9600

Note:  I believe the plan's can be scaled up or down instantly without display and pausing a service stops the Power BI embedded costs.  If you run over the Max requests per hour I believe the Power BI PaaS will still serve up page/reports but you will get an extra bill for the additional reports.

2.> You'll also need to purchase at least 1 Power BI Pro licence, that is used for: administration, content publishing, and development..
3.> As of time of writing (Nov 2017) the Microsoft Power BI Gateway does not offer High Availability (HA), but I'm sure it is coming soon.
4.>  A single account is used to connect to each source and RLS security has to be applied at the source (SQL SSAS), user table mapping is required.

Also see:
http://blog.sharepointsite.co.uk/2017/10/power-bi-on-prem-extranet-information.html
https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security

SharePoint on-premise reporting options

Overview:  As always this really depends on the circumstances but my broad view on reporting for SharePoint is outlined below.

Thoughts:

• Power BI on-prem. has not been release (at the time of this blog) but will work for SSRS reports and have the Power BI functionality that shall be embedded using an iFrame into SharePoint 1st edition only.
• The upgrade path from SSRS SharePoint integrated mode is going to be hard, whereas the upgrade path from SSRS native mode will be simple.
• Power-BI on-prem is not going to support SAML.  Use WAP (Web Application Proxy)/reverse proxy to get NTLM ot Kerbros tickets for authentication.  Only config is ADFS+WAP to access PowerBI on the Extranet.  If you use another Federation service will need to do a passive claim.  For Example if you use SiteMinder, you need to pass onto ADFS + WAP
• SSRS SharePoint integrated mode shall be available only in SP2013 and SP2016 and not have any continued path going forward.

SharePoint 2013 Options:

SharePoint 2013 BI Options	SharePoint 2013 SQL BI Options
Excel Services PerformancePoint SharePoint KPI/Filters	SSRS SharePoint Integrated Mode Power Pivot for SharePoint

The table below shows what you client probably uses historically and the challenge is to map them out to a supportable solution on SharePoint going forward.

BI in SharePoint and the Microsoft stack has drastically changed over the past 2 years.  The next 12 months should keep this pace up with the release on Power BI.  As my knowledge in this area improves I'll update this post.

Other Posts that may be useful:
A good post to read as of EOY 2016 on BI  for SharePoint
SP2013 with SSRS 2012 SharePoint integrated Mode Overview
Installing SSRS on SP2013 Reporting Post

SSRS 2012 with SP2013 component diagram

I like diagrams as they help me understand faster.  I wrote a post about 9 months ago about installing SSRS for SP2013, and recently a friend called me to moaned about the documentation available and I remember it being very poor.  My posts cover the topic be re-reading the posts and it could be clearer.  The key is to understand the pieces and where they sit.  He had seen my posts but some related to SP2010 and the 1 specific post relating to SP2013 using SSRS 2012 is not instantly understandable.  It has valuable info but where the parts/components reside is not perfectly clear so I put this diagram together.

There are only 4 parts, the fewer servers to have the easier it is to do theses steps.  I.e. on 1 server farms like dev it's pretty easy as all the SQL components are installed hopefully during the initial SQL Server 2012 SP1 install.

Part 1, you need a SP 2013 farm
Part 2, you need to have a database with the SSRS components/functionality install.  Now if you are using the same database as your SP database ensure the SSRS components were installed at the initial build.  This diagram assumes you have a separate SQL instance which is pretty reasonable.
Part 3, you need to run a core SQL install (only needs the SQL component relating to SSRS) on the SharePoint App/web server.  This 1 or more servers containing the SharePoint binaries.  This is the step that is new/different and most people don't do.
Part 4, run some Powershell to create the SSRS SSA and hook up the relationship.  This is done on any of the SharePoint servers such as your main CA box.

This post couple with the overview should help you understand the components needed for large or automated installs.
http://blog.sharepointsite.co.uk/2013/08/installing-ssrs-2012-on-sp2013-within.html


====================


Tip: If you need to move your Reporting Services database to another server, you need to manually add the RSexecRole to the new SQL Server.

Importing an SSRS report and displaying it on a new environment

This is a 3 Series Post on SSRS 2012 for SharePoint 2010

Part 1 - Installing SSRS 2012 in SharePoint Mode on SharePoint 2010

Part 2 - (This Post) Importing an SSRS report and displaying it on a new environment

Part 3 Update - Installing SSRS 2012 SP1 onto a SP2013 Farm

Overview:  This post looks at moving your SQL Server Report Services Report into a new farm (e.g. production).  I use the UI in this step-by-step guide.  I am using SQL 2012 for SSRS and SP 2010 as created in this post.

Assumptions:

1. SP 2010 WFE's
2. SQL 2012 SP1 is the database server
3. Report (.rdl) is already created and uses a .rsds file to specify the connection string. 

Steps to migrate and deploy an existing SSRS report:
1. CA > Application Management > Manage Service Applications.

2. New > SQL Server Reporting Services Service Application > Fill in as shown below:

3. Verify the Service Application is running.













4. Once Completed.  Open a Web Application that has the SSRS Service Application associated to it so we can test it out.  Login to a site collection and create a new library based on the "Reports Library" template.

5. Upload a report (.rdl) and the Connection (.rsds) file int he new "Report library". Then click the dataset/connection (.rsds) file, and test it is working.

6. Edit the binding of the rdl to it rsds as shown below:

7. Ensure you have the SP, scheme ect on the database server to perform the query logic.
8. Click on the report (rdl) file to see it working.

Tip: The install is fragile in that if the order, versions or settings are not right it doesn't work without giving error often.  Make sure SQL Servsions on all machines are the same!  I got caught by not having the same SQL version on 1 WFE as the backend SQL SSRS server.
This is a 3 Series Post on SSRS 2012 for SharePoint 2010

Part 1 - Installing SSRS 2012 in SharePoint Mode on SharePoint 2010

Part 2 - (This Post) Importing an SSRS report and displaying it on a new environment

Part 3 Update - Installing SSRS 2012 SP1 onto a SP2013 Farm

 

Also I have an updated post on using SP2013 to us SSRS in SharePoint mode here.

 

Update: 24 Jan 2014 - PS to upload rdl's and data sources into a reporting library

 

$spWeb = Get-SPWeb "http://demo.dev/"

$spList = $spWeb.Lists["SSRS"]

$file = [io.file]::ReadAllBytes('C:\test.rdl')

$spList.RootFolder.Files.Add("test.rdl",$file)

# This code finds the SSRS reporting library called "SSRS" and takes a rdl file off the local drive and adds it to the library.

SharePoint 2010 simple reporting options

Overview: SSRS has it's place but I want to gather reports from a composite application where data is stored in SharePoint lists.
Solutions:
OOTB web parts - UI may be tricky to get right but useful for quick reporting that can be performed by power users for general business users. Use filter, List View (XLVWP), CQWP, data View, chart web parts.
Custom Web parts - either write or buy web parts for querying lists. Custom CAML queries provide a solution to display data. Con is that it's pretty development heavy, inflexible and requires code deployment. SPMetal has issues with joins, publish columns and hidden columns such as "Created by". Also see Ninetix reporting.
Dataviews - Displaying list data using SPD - need SPD access. Joining 2 lists.

Giles Hamson has a good chart explaining reporting options.