none
Olap viewing question. RRS feed

  • Question

  • My question is this.. Say a user has full rights to a cube thus he is able to see all the sales data for our company say this user is the director of sales.  Now the director has sales managers that work for him, and the managers have brokers that work for them. 

    Now then.. I have given the director full rights, and I have given the sales managers and brokers read only to there dim's of the cube this is all working when they create there own reports or refresh a existing report.  but if the director creates a excel report and sends that report out to the field without picking the sales manager/brokers individually in the page header of the excel pivot. Each user is able to see everything that the director was able to see that is until the user clicks the refresh button.  Then and only then it hides the rest of the data. Is there a way to stop this without having the director make 40 saves for the same report?

    I guess u can say i really have a excel caching problem with all the data going out.

     

    example:

    director makes a report:

    sales by customer, by month

    *all data shows on the report*

    saves the file to a local share.

     

    manager A logs on sees the report downloads it, opens it and sees all data for manager B because the director didn't do a sales by manager, by customer, by month because the director thought that the cube only shows Manager A's managers A info not A and B. 

     

    I will gladly explain anything else on this problem.. i really am at a loss on what to do.

    Tuesday, April 10, 2007 4:39 PM

Answers

  • If the manager do the report in Excel and slice the report by manager(get a listbox of the managers and not placing them on the row or column axis) a first requirement is fullfilled.

     

    Next you will need to create separate roles for each manager in BIDS(BI-Dev Studio) and map their windows identity to these roles.

     

    Finally, your manager need a portal like Sharepoint Services 3.0 (free) or Sharepoint Server 2007. With this portal he/she can create a webpart and connect the spreadsheet (XL) to that webpart.

     

    There is more to this story but this the big picture of what to do.

     

    You will need a portal, like Sharepoint and cube roles in SSAS2005 to manage this.

     

    HTH

    Thomas Ivarsson

    Tuesday, April 10, 2007 6:09 PM
    Moderator
  • Hello! You have some virtual labs, for free here: http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx

     

    A. Correct but you will need to create separate roles in the cube for each manager.

    B.Correct

    C. Yes, if you have created all under A.

     

    Sharepoint Services 3.0 is free under a Windows 2003 license.

     

    For details about Sharepoint you will have to post to that news group.

     

    Regards

    Thomas

    Tuesday, April 10, 2007 7:18 PM
    Moderator
  • It's a little more complex than that. Just saving the Excel document to Sharepoint will not force a refresh when it is opened. This will only work if you use Excel 2007 and Excel Web Services, and publish the spreadsheet to Excel Web Services. This requires Sharepoint Server and a Sharepoint Enterprise CAL for each user.

    You could use an Office Web Component web part (what I think Thomas was referring to in his original post) on a Sharepoint page, which will refresh each time the page is viewed. However, this has to be configured directly on the web page, rather than being published from an existing spreadsheet.

     

    There are a lot of options for displaying this information online. Another good tool for this is ProClarity (which can be used with or without Sharepoint). You could use Report Builder (part of Reporting Services) to allow the manager to create the report, and publish it to a Report Server.

    Wednesday, April 11, 2007 11:54 PM
  • You can force Excel pivot tables to refresh on open, you right click on the pivot table, goto table options and turn on the Refresh On Open setting. (I'm pretty sure this setting has existed for the last few versions - Excel 2003 definitely has it)

     

    The only problems are that you have to manual do this for any pivot table you create and I think there may be a few seconds where you might be able to see someone else's data before the refresh kicks in (depends on how long your queries take to run). You could probably automate the setting of this property with a macro, but macros cause Excel to complain about potential security threats, so that could pose its own set of issues.

    Thursday, April 12, 2007 5:16 AM
    Moderator
  • I believe the auto refresh is optional - Excel prompts you, but you can cancel it.
    Thursday, April 12, 2007 10:21 PM

All replies

  • If the manager do the report in Excel and slice the report by manager(get a listbox of the managers and not placing them on the row or column axis) a first requirement is fullfilled.

     

    Next you will need to create separate roles for each manager in BIDS(BI-Dev Studio) and map their windows identity to these roles.

     

    Finally, your manager need a portal like Sharepoint Services 3.0 (free) or Sharepoint Server 2007. With this portal he/she can create a webpart and connect the spreadsheet (XL) to that webpart.

     

    There is more to this story but this the big picture of what to do.

     

    You will need a portal, like Sharepoint and cube roles in SSAS2005 to manage this.

     

    HTH

    Thomas Ivarsson

    Tuesday, April 10, 2007 6:09 PM
    Moderator
  • I'm a little lost on the sharepoint i'm researching it now.  but it appears to be more of a document center for a company ( i'm could be wrong here).

    So what your saying is I:

     A. give the director all access,

    B. have him save the file to a sharepoint server. and

    C. when a manager connects to that doc in sharepoint it will refresh the data and only show that manager his data, basied off the windows log in.

     

    Right now I have part A done, and I do have cube roles all ready setup i am using 2005.  So i'll i'm missing is sharepoint then? right?

    Tuesday, April 10, 2007 7:10 PM
  • Hello! You have some virtual labs, for free here: http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx

     

    A. Correct but you will need to create separate roles in the cube for each manager.

    B.Correct

    C. Yes, if you have created all under A.

     

    Sharepoint Services 3.0 is free under a Windows 2003 license.

     

    For details about Sharepoint you will have to post to that news group.

     

    Regards

    Thomas

    Tuesday, April 10, 2007 7:18 PM
    Moderator
  • It's a little more complex than that. Just saving the Excel document to Sharepoint will not force a refresh when it is opened. This will only work if you use Excel 2007 and Excel Web Services, and publish the spreadsheet to Excel Web Services. This requires Sharepoint Server and a Sharepoint Enterprise CAL for each user.

    You could use an Office Web Component web part (what I think Thomas was referring to in his original post) on a Sharepoint page, which will refresh each time the page is viewed. However, this has to be configured directly on the web page, rather than being published from an existing spreadsheet.

     

    There are a lot of options for displaying this information online. Another good tool for this is ProClarity (which can be used with or without Sharepoint). You could use Report Builder (part of Reporting Services) to allow the manager to create the report, and publish it to a Report Server.

    Wednesday, April 11, 2007 11:54 PM
  • You can force Excel pivot tables to refresh on open, you right click on the pivot table, goto table options and turn on the Refresh On Open setting. (I'm pretty sure this setting has existed for the last few versions - Excel 2003 definitely has it)

     

    The only problems are that you have to manual do this for any pivot table you create and I think there may be a few seconds where you might be able to see someone else's data before the refresh kicks in (depends on how long your queries take to run). You could probably automate the setting of this property with a macro, but macros cause Excel to complain about potential security threats, so that could pose its own set of issues.

    Thursday, April 12, 2007 5:16 AM
    Moderator
  • I believe the auto refresh is optional - Excel prompts you, but you can cancel it.
    Thursday, April 12, 2007 10:21 PM