locked
Offline Reports in Microsoft BI RRS feed

  • Question

  • Hi,

    Requirement: I am looking for an offline reporting capability in Microsoft BI. In my case, I have around 1000 users who need an offline reporting access as well as the data should be restricted as per user role(role base data security). Also the data should be refreshed once the user have access to internet.

    What are the tool composition required in client and Server and how the below requirement can be achieved?

    • Offline report Access
    • Data refresh approach, once the user connected to internet
    • Data Security -  Role based data Security

    Can be done using PowerPivot with Excel? Microsoft OLAP Cube with Excel? How?

    Thanks,



    Liyasker Samraj K

    Wednesday, May 14, 2014 12:16 PM

Answers

  • using SSAS on top of SQL Server is a very common scenario to solve security issues as impelementing row-level security in SQL Server is a real pain and also tends to be very slow

    so you would basically create a power pivot model that pulls data from SSAS using integrated security
    whenever a user refreshes the model, he will only see the data he or she is allowed to see assuming security is defined on SSAS which is a must-have for this solution to work

    depending on the reporting requirements you may only need one model
    you may also create several model e.g. one for normal employees, one for team leads and another one for managers

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Elvis Long Monday, May 19, 2014 10:03 AM
    • Marked as answer by Elvis Long Friday, May 23, 2014 1:43 AM
    Thursday, May 15, 2014 3:38 PM
    Answerer

All replies

  • ok, there are several other things to consider:

    - does offline report access mean that the user is able to slice&dice, filter, etc. offline?

    - "connected to the internet" means without VPN?
    without VPN you would need a public interface for SQL or SSAS or wherever you pull data from!


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, May 14, 2014 12:26 PM
    Answerer
  • Thanks Gerhard Brueckl !

    #1 - Yes need an ability to slice & dice and filter - kind of Ad-hoc

    #2 - Yes user can connect the VPN once they have access to Internet.   (keen to know how it’s feasible without VPN, just for the knowledge, but primarily I need an answers for the above questions)

    Thanks,


    Liyasker Samraj K

    Wednesday, May 14, 2014 1:28 PM
  • ok, you would need the following:

    1) a service which provides the base data
      this could be SQL Server, Analysis Services or whatever you like
    2) security has to be defined on that service
    3) then create a power pivot model that pulls data from that service using the current users credentials
    4) create pivot tables, charts, reports etc in the same workbook
    5) send out an empty version (created with a user without access) of that power pivot workbook to your users

    once the users are connected to the internet/VPN or basically have access to the service, they can refresh the workbook using their credentials 
    once the power pivot model is populated they can use the reports/charts, etc and have ad-hoc slicing&dicing capabilities

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Thursday, May 15, 2014 8:07 AM
    Thursday, May 15, 2014 7:01 AM
    Answerer
  • Thanks Gerhard Brueck for your help!

    In my case, I have the Warehouse in SQL Server 2008 database. So when I write Query the pull the data to Power Pivot, how do I control the data only relevant to the Users.

    Also how many Power pivot model do I need? How the user name can be passed across SQL database <- Power Pivot model <- Excel 2010.

    Here we have 1000 Executives, everyone should see the data of themselves and executive working under them.( I have the experience of implementing security in SSAS with PPS (or) SSRS reports in SP. But Power Pivot, I could not visualize)

    In Client side, hope Excel 2010 with Power Pivot add-in is sufficient for the requirement.

    Thanks again!


    Liyasker Samraj K


    • Edited by Liyasker Thursday, May 15, 2014 3:00 PM Typo
    Thursday, May 15, 2014 2:58 PM
  • using SSAS on top of SQL Server is a very common scenario to solve security issues as impelementing row-level security in SQL Server is a real pain and also tends to be very slow

    so you would basically create a power pivot model that pulls data from SSAS using integrated security
    whenever a user refreshes the model, he will only see the data he or she is allowed to see assuming security is defined on SSAS which is a must-have for this solution to work

    depending on the reporting requirements you may only need one model
    you may also create several model e.g. one for normal employees, one for team leads and another one for managers

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Elvis Long Monday, May 19, 2014 10:03 AM
    • Marked as answer by Elvis Long Friday, May 23, 2014 1:43 AM
    Thursday, May 15, 2014 3:38 PM
    Answerer