Viewing SSAS cube thru Excel

  • We're using Excel PivotTable Chart & Report (temporarily) to view SSAS cubes. We're using Office XP(2002). Is there something I have to load on the client machine in order for them to read the SSAS 2005 cube. They have the option to connect to SSAS OLAP 8.0, but they don't have the option for SSAS 9.0 in the drop-down.

  • you'd need the OLAP addons for excel.

    (downloadable at MS)

    But I've also heard that you would need office 2007 :doze: if you want to avoid anoying bugs.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I, personally, have ExcelXP, but I've got the SQL2005 client tools loaded. I assume that's why I can see the data in Excel. How about Excel2003, will that work? Or do I have to either:

    a) download the OLAP Excel Addons or

    b) upgrade my users to Excel2007?

  • I haven't experienced on excel with SSAS myself, but hope soon to

    do so.

    I'm still figuring out what kind of buggs or issues there are with office 2003 in combination with ssas.

    Maybe some other forum members can add their findings. :discuss:

    So I'd start with downloading and installing the addons.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • OK, had the users install the msxml6 file and the ASOLEDB9 file. They get the option to choose the OLAP 9.0 in the drop down now, however, they cannot see the cube I created. I know I published the cube, but can't figure out why the users can't choose it when they hit the "Connect" button.

  • Did you grant read permissions to the designated users ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • They already had read permissions on the database. I used my credentials on their machine when connecting, and that did the trick. However, I sure don't like doing that. Is there more security on the cube somewhere that I don't know about? I haven't gotten to the part in my book about security yet.

  • There are many levels you can grant read access.

    I'm afraid I cannot guide you through it at this time because I haven't played

    around with it to know it to the bone. :ermm:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I have found with Excel 2003 is that is works well without the special additions, but it lacks features such as the drill through ability that a lot of users find helpful. This is supposedly addressed by additions you can download and install, but we we tested it, the data was never what it was in the cube and it was difficult to create the pivot table in the new tools.

    Excel 2007 is significantly better with its OLAP interface and also has the highly desirable drill through capability. You can refresh all the pivot tables in a single work book at once instead of having to go through one at a time, the formats set in the cube are now automatic, as opposed to constantly having to be formatted appropriately, and you can even create local cubes that can be used when disconnected from the network. Finally the increased columns means you can freely put dimensions on either axis without running out of room.

    Bottom line: if you are using Excel to access your OLAP data and can upgrade to Excel 2007, do so. If you can't, I don't recommend the Microsoft tools--just use Excel 2003 to the extent that the normal program allows it.

  • We use Roles to grant permissions on our SSAS Cubes so users can access the cubes through Excel 2007.

    Check out this link.

    http://www.technet.microsoft.com/en-us/library/ms174786.aspx

  • indeed, you _must_ use roles ( if you don't want every user to be full SSAS system administrator :crazy: ), but the level of granularity is something you'll have to determine yourself.

    And you can apparently even go up to cell level :sick:.

    And determine if a user can read or process to the objectlevel you want.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • OK, I assigned roles to my cube and now everyone is happy. At first I gave everyone the lowest security, Read, and they could see the cube, then they could see the fields, but they couldn't see the data in the graph. Then I gave them the second level of security which I believe is Process, and they still couldn't actually see the bars on the graph. Then I gave them the 3rd level, Administator methinks, and then the data actually showed up in the graph. My company is psyched. I think they're gonna be WOW-ed by how this thing can perform. Now I've gotta learn this MDX thing and how to hook up SSRS to it.

    Thanks for all the help in this post, everyone. I'm sure I'll have more questions in the near future.

    Also, I looked for that article on technet from jim.pennington. Evidently it doesn't exist anymore or it's been moved.

  • I'm running Excel 2007 on 2GB Machine accessing a 2008 SSAS cube using Pivot Tables. Problem is that after 14000 rows and 6 columns excel runs out of resources and all comes to a grinding stop. I changed the cube to 1 Fact and 1 Dimension but the same result. Surely excel should be able to access more records? Any suggestions?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply