locked
Power Pivot and Solution Building RRS feed

  • Question

  • Good day to everyone out there.

    I am hoping that one of you power pivot guru's out there will be able to clarify something for me.

    I am in the business for creating reports for different industries. In the past I have built solutions for many verticals using a reporting application that leverages of the power of Excel. I have always backed Microsoft and now really want to sink my teeth in building solutions on to of PowerPivot. I do have some concerns though, and I really help someone can take a moment and give me some insight.

    The thinking at this stage is that PowerPivot will be connected to a single data source (Possibly and one or two more as time goes on), the solution that I need is in a vertical where a lot of people are pushing for the knowledge that I have about the data sources. That’s the problem . . . I don’t want to expose the queries that I have and on top of that I need the queries to be house in an administration section with securities so that only selected end users can access them. Does this mean that I have to create SSAS cubes and deploy those, as well as have PowerPivot within Sharepoint to have a central administration of the PowerPivot Reports?

    I guess if I were to try and summerize, how would one create a solution on top of PowerPivot, and maintain comfort that the IP is secure and there is a single version of the truth.

    Many thanks all, and I look forward to your response.

    Tuesday, November 1, 2011 5:49 AM

Answers

  • I suggest reviewing the available formats of loading data available to the PowerPivot window.  There is, for example, text file source.  Following the same example above, you could have some automated procedure to dump data into a file which can then be used to populate a PowerPivot model.   In a similar fashion to the example above, your users won't know about the database query logic used for selecting data but will still be able to slice and dice it on the report.

    Hope that helps!

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    • Marked as answer by Challen Fu Monday, November 7, 2011 10:37 AM
    Tuesday, November 1, 2011 8:58 PM
    Answerer

All replies

  • hi Excel_Man

    within PowerPivot development, you always have the option of using existing database artifacts like stored procedures and views.  In other words, you dont always have to access the query directly.    If you decide to wrap your logic within a stored procedure then you can secure it or even add identity based permissions to 'emulate' the lacking row level security feature.

    having said that, the 'new wave' of self service BI tools is built on a philosophy of open (or increasingly open) access to information, so it is likely you will get stronger demand for openess in the future.

    HTH

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    Tuesday, November 1, 2011 5:35 PM
    Answerer
  • Many thanks for your respone Javier,

     

    Whilst I understand the new drive to self service BI, and I do because I play in that game. The need to create solutions for software vendors is a very important role in the expansion of Microsoft BI. Solutions are required. However if a developer cannot go add procedures to dbs and then would I be correct in saying that a cude is the only other option then would be a cube, and are cudes protected IP?

     


    Wyndham Boonzaier Excel Integration Affordable Excel Solutions
    Tuesday, November 1, 2011 6:06 PM
  • I think cubes will give you more control, yes.  But it is not the only option at all.  You can, for example, publish your data to some url in OData format which makes it a valid source for PowerPivot.  In that way you shield users from the underlying query that created that data set.

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    Tuesday, November 1, 2011 6:34 PM
    Answerer
  • Hmmmm, now that is interesting. Thanks for that. Clearly still a lot to learn. Could you point me in the direction of other options too?


    Wyndham Boonzaier Excel Integration Affordable Excel Solutions
    Tuesday, November 1, 2011 7:10 PM
  • I suggest reviewing the available formats of loading data available to the PowerPivot window.  There is, for example, text file source.  Following the same example above, you could have some automated procedure to dump data into a file which can then be used to populate a PowerPivot model.   In a similar fashion to the example above, your users won't know about the database query logic used for selecting data but will still be able to slice and dice it on the report.

    Hope that helps!

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    • Marked as answer by Challen Fu Monday, November 7, 2011 10:37 AM
    Tuesday, November 1, 2011 8:58 PM
    Answerer