Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

OLAP Cube: Export Dimension Hierarchy (Visual Studio/MSSQL/MSAS)

Posted on 2008-06-19
6
Medium Priority
?
1,305 Views
Last Modified: 2011-10-03
Hi,

I have a HOLAP cube with a large number of dimensions. I've jsut been asked to produce something ina  tree format that can be displayed to the client. I would just do it by hand except that a) the list is quite large (45 dimensions, each with around 3-4 granulatrities) and b) the dimensions are soon going to be changed when the same thing will be required again - so I'm looking for the lazy option.

I'm not overly fussed about the format (screenshots were a possibility) as long as it is (or can be used to generate) a graphic tree-style view.

Any help greatly appreciated
0
Comment
Question by:basiclife
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21822997
You could access the cube with a pivot table in Excel and then expand down all the dimensions, wouldn't take too long.

Or use reporting services to access the OLAP with a Matrix report and then let the client drill down themselves (You could probably even put dummy or NULL data in there)
0
 
LVL 5

Author Comment

by:basiclife
ID: 21823208
Thanks for the rapid response. Can you explain the process for the Excel PivotTable? I've used PTs before and am familiar with their operation but have only ever used another worksheet as a data source. I've installed "Microsoft Query" which seems to be required to use an external data source and have managed to establish a connection to the database the data is pulled from but cannot see any way to connect it directly to analysis services to query the cube.

Since the dimensions are just views and tables until they're pulled into the cube and organised into a hierarchy, I'm obviously missing something here.

Thanks again

Basiclife
0
 
LVL 5

Author Comment

by:basiclife
ID: 21823222
Hang on, I've just seen the glaringly big "OLAP" tab which I managed to ignore previously *sigh*

Be back to you in 10...

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 5

Author Comment

by:basiclife
ID: 21823389
OK, I've managed to get the PivotTable working (Never knew you could do that - It'll come in handy for some reporting stuff - Thanks!) but I can't see an easy way to get the list of fields to fromt eh field list into the document proper - except by using the in a PT of course which would be overly complex if I used all of them.

Additionally, I'm again not sure if I'm missing something but... The top level of each hierarchy is shown along with some of the sub-levels but some seem to be missing eg:

We are using a computed column for customer age. We have a table matching the ages to the (irregular) age bands requested. The structure is simply: <root>/AgeGroup/Age

However, the list of fields shows only Age not AgeGroup.

Any suggestions?
0
 
LVL 13

Accepted Solution

by:
rickchild earned 2000 total points
ID: 21823757
Strange about the missing age group, sounds like a measure rather than a dsimension?

If you can fix that you can also save a static copy of the pivot, which you coudl then send out to client.

Other than that all I can think of is a SSRS Matrix report through the wizard, should be quick to set up and can be exported to Excel.
0
 
LVL 5

Author Closing Comment

by:basiclife
ID: 31468816
Good answer, thanks very much! Exactly what I was looking for and I got it working after only a little messing about.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question