Category Archives: Reporting

Power BI Data Security – Power BI Report Server

Power BI Security LogoPower BI Report Server was released as a way to host reports on premises. It was one of the highest requested features for Power BI. Power BI Report Server offers a subset of Power BI Service capabilities and as such does not have the rich collaborative or security options as seen in the service (online). I have a number of other thoughts on this tool, but that is not the purpose of this post. I am often asked in sessions on Power BI Data Security about Power BI Report Server. The problem is that Power BI Report Server is not what Power BI was intended to be and as such security is very different.

Power BI Report Server Uses Files and Folder Security Model

Unlike Power BI Service which leverages the Office 365 security model with workspaces and apps, Power BI Report Server only supports deploying Power BI Desktop files as Power BI Reports in SQL Server Reporting Services. Each file is uploaded to the Report Server and it is viewed by opening the report.

Power BI Report Server - Portal

You have essentially three layers of access to the report file security in Power BI Report Server.

  1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
  2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
  3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server Report Nuances

If you have read many of my posts around Power BI Data Security, I have discussed gateways, workspaces, and even Office 365 groups. The following items are uniquely related to Power BI reports stored in the report server.

Data Sources

Because it is not possible to use a Reporting Services Shared Data Source with your Power BI reports at this time (not sure if this will change), the data source information will need to be managed by report. This is not dissimilar to the Power BI Service. However, for on premises data we use the gateway with the service. There is not a common or shared data access feature in Power BI Report Server. Data sources are included in the report and can be managed in the deployed report by clicking the ellipses on the icon and selecting manage.

Power BI Report Server - Data Source

Only the report creator, Content Managers, and Publishers have permissions to see and modify this information.

Protecting Data Sources

One concern raised is whether data sources with credentials are downloaded when the Power BI file is downloaded. First key idea is that only users with the Publisher and Content Manager roles can download the files

When a Power BI report file is downloaded, you are prompted for credentials when refreshing data. Passwords are encrypted so they must be reentered when the file is opened or data refreshed in the desktop.

Mapping SSRS Roles to Power BI Functions

The portal, folders, and files are secured using SSRS roles. Here is a high level summary of the roles and how it impacts Power BI Reports

  • Browser – This is similar to a read only function or if you would deliver the report as a Power BI App in the service. This should be used by anyone who needs access to the report, but does not create content.
  • Content Manager – This is for a content creator with admin privileges. This role can manage content and user access.
  • Publisher – This is for content creators who do not need to manage users. Most content creators fall into this category.
  • Report Builder – does not apply to Power BI Reports which use Power BI Desktop to develop reports.

Avoid Deploying to the Portal Home Page

I would recommend you not allow Power BI reports to be deployed to the primary portal, but create folders to manage the group of content creators and managers based on the department or group level needs.

Reports deployed to the home page of the portal should be managed as enterprise assets. Use the folders and related security groups to manage who can deploy at this  level.

Wrap Up

I would like to extend a special thanks to Kathy Vick, Principal Consultant at Pragmatic Works. She provided guidance on implementing Power BI Report Server. Thanks Kathy for the help on this topic. Check out Kathy’s Twitter (@MsKathyV)  and LinkedIn account to learn more.

Check out more about Power BI Data Security in my blog series.

Advertisements

PowerPoint–My Dashboard and Report Design Tool

At some point I think that I am becoming a Microsoft OfficeMALL13_Badge_See125x125 specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

Microsoft Powerpoint 2013 IconSo I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

image

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

image

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

image

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.

Oracle Tips for MSBI Devs #3: Choosing Drivers

When working with Oracle, drivers are truly a pain to get working correctly.  I will discuss my preferred choice and why for the following tools – SSIS, SSAS, and SSRS.

SSIS Drivers

Without much question, you should use the Attunity tools for working with Oracle data in the Data Flow task.  In SSIS 2008, the SSIS Connector is free and can be found here:  http://www.microsoft.com/download/en/search.aspx?q=oracle%20connector.  It includes the connection manager, source component and destination component.  Without a doubt this is the only way to work with Oracle data components in the Data Flow task.  (NOTE: I cannot find the SSIS 2012 equivalent at the moment.  However, Matt Massan’s blog post after PASS Summit 2011 notes more work is being done with Attunity.)  UPDATE: I wrote this prior to a blog post from Matt Massan on support for SSIS 2012 with v2.0 of the Microsoft Connector.  Check out Matt’s update on this: http://blogs.msdn.com/b/mattm/archive/2012/04/04/microsoft-connectors-v2-0-for-oracle-and-teradata-now-available.aspx.

However, this connector does you no good when working with the Execute SQL task.  In SSIS 2008, I use the OLE DB provider from Oracle to create the connection used with Execute SQL Task.  In my work with procedures in my first tip, I used the OLE DB provider with 2012 as well and it worked fine.

SSAS Drivers

When working with SSAS 2008 and, until I know differently, SSAS 2012, I would recommend using the Oracle OLE DB driver from Oracle.  This driver is not the fastest I have seen (third party drivers are marginally faster and the .NET driver is faster as well), but it has provided consistent results for the right price.  Third party drivers will improve the throughput, but not substantially.  The Oracle provided .NET driver is faster as well, but has an unchangeable active query timeout of one hour.  If you have any processing times that exceed this, it will unceremoniously drop the connection.  For these reasons, I have stuck with the OLE DB provider from Oracle which is not necessarily the fastest, but it has been the least painful to work with.

SSRS Drivers

It is with SSRS I have seen mixed results.  Primarily because of the better performance in the Oracle .NET driver.  If you can guarantee that your reports will return their data in under an hour, this seems to be the best option.  However, if you want to manage to a single driver set across all tools, you may find that the management of the OLE DB driver as the only driver makes sense in your organization.

Test, Test, Test

I have given you my experience using the drivers above.  However, you may find value in purchasing a third party driver or you may find a different experience when you implement in your environment.  Be sure to test and understand the implications in maintenance and system cost when choosing different drivers across your solutions.

SQL PASS Summit–Day 3 (Keynote)

From the PASS president, Rushabh Mehta.

PASS has facilitated 430,000 hours of training and expansion into the global community.  These three days contain massive amounts of training and opportunities to network with other SQL peers, including over 800 first timers.  SQL Server MVP Deep Dives Volume 2 has been officially released here with the opportunity to get a bunch of autographs from the 55 MVP authors in the house.

Ted Kummert’s Keynote highlights:
  • “Some database vendors just decided to get into the cloud last week”
  • Growth of PASS is amazing! Over 240 chapters, 300 MVPs, 79,000+ members, and over 4000 attendees at the Summit.
  • SSAS in the cloud, coming soon?
  • Crescent is now PowerView.
  • SQL Server 2012 (aka Denali) will release in the first half of next year.
  • MS SQL Server Vision: “any data, any size, anywhere”
  • Big Data characteristics:  Large data volumes, tranditional and non-traditional data sources, new technologies and new economics and new insights.
  • They will be supporting Hadoop on Windows. By providing connectors to SQL Server and SQL Server Azure, by investing in a JavaScript Framework for Hadoop, drivers for Excel, by partnering with Hortworks,
  • From Denny Lee: HiveQL – the query language for working with Hadoop data. Hive ODBC Driver in PowerPivot – this driver will be available in the next month.
  • Announcing code name Data Explorer for data enrichment, which will be in SQL Azure Labs by end of year.
  • Data Explorer will be in SQL Azure Labs by end of year.  It allows you to do data classifications.  Looks very much like a variation of Excel.  It allows you to add data from external data in the Azure Marketplace which will recommend data based on the data you are analyzing.  Kind of looks like PowerPivot in the cloud, interesting.
  • Amir Netz was recently promoted to Technical Fellow at Microsoft – early developer of OLAP Services, SSAS and beyond.
  • Amir demos PowerView.  During the demo we discovered that Meet the Fockers was the top comedy.  Oh, the power of BI.  Great demo about this product.  He also announced that export to PowerPoint will be supported in this release.

Quite the whirlwind this morning, but some more cool BI stuff coming soon.  Looking forward to the rest of the day.

March Presentations Uploaded on SkyDrive

I presented at three events in the past 10 days. 

  • Magenic Lunch & Learn – SSRS Training – Overview of SQL Server Reporting Services functionality.
  • Minnesota SQL Server User Group – SQL Azure – a good discussion on SQL Azure, it’s capabilities, and cost effectiveness for our solutions.
  • Techfuse 2011 – Performance Monitoring and Tuning with SSAS – a look at the query execution workflow and the tools to monitor and tune Analysis Services.

I hope these provide value to you.  Feel free to comment here with questions related to any of these presentations.