Question on OLAP reporting

[FrankBoekamp]

Hi There,

We are starting to explore the possibilities of SAS OLAP. So far it's very useful to us. A SAS user from our company has following question about using OLAP cubes:

Is it possible to create following report from an OLAP cube in an easy way?

		Hospital1	Hospital2
admission_type	emergency department operating theatre …	300 100 100	..
admission_source	Ward ER	344 23	..

As I understand it all OLAP dimensions are hierarchical, so how can this be accomplished in an easy way?

Cheers,

Frank

[FrankBoekamp]

No reply :smileyconfused:

Does nobody else needs this?

[ChrisHemedinger]

Frank,

One tricky bit might have to do with the hierarchies.  You've got admission_type as one dimension, and admission_source as another.  Unless those roll up to another category, it might not be easy to get them to appear one on top of the other as shown in your example.

If not, you can create two slices: one with admission_type and one with admission_source, and then combine those outside of the OLAP analyzer using other EG tasks.  However, I assume that you don't want to aggregate these two dimensions together, adding the counts for "source" to the counts for "type", since that would double-count each event.

A new blog on the scene that covers OLAP topics:

http://www.bi-notes.com/

As well as Angela's blog:

http://blogs.sas.com/content/bi/

Chris

[FrankBoekamp]

Hi Chris,

The problem indeed is that the dimensions are not hierarchical to each other. The solution with the slices works nice but can be a big effort if you have lots of variabels to report on.

Do you know whether this is a restriction in SAS OLAP or a restriction in OLAP in general? Could it maybe be accomplished using some alternative MDX code?

Cheers,

Frank

[Cynthia_sas]

Hi:

  Another county heard from...

   This sort of looks like a PROC TABULATE or Summary Tables Task to me. The data wouldn't even have to be in an OLAP cube to generate a report like this. The interior table lines might be an issue, since SAS isn't Word or Excel in making tables, but the table itself should be do-able.

cynthia

[TomKari]

Count me in with Cynthia. This is one of the things that the Summary Tables task is ideal for. Try this with SASHELP.SHOES, and see if it's what you're after. Using Product as a proxy for admission_type, and Subsidiary as a proxy for admission_source, with Region across the top for Hospital, you'll get Product by Region, followed by Subsidiary by Region, the two subtables independent of each other.

(Excuse the 4.1 format...it's what I have at home.)

[LinusH]

I think we need to know the reason for using an OLAP cube.

It's concets quite different (OLAP and using tasks in EG).

And yes, you could probably get the data from the cube using MDX, but then you are responsible for putting it togehter by your self...

For me, it look like two different queries, and the measure in the columns aren't possible to sum, right?

So, I think it would be clearer to have these two hierarchies' measures displayed in two separate tables, and that could be achieved by using WRS and Infomaps.

/Linus

[ChrisHemedinger]

Hear that? Tom runs EG at home. Now *that's* dedication. 

[FrankBoekamp]

Thanks for all your answers.

Proc tabulate will do for certain. (Also proc freq and proc univariate van be very usefull)

But for efficiency an simplicity reasons it would be nice if an end user could make those type of reports from a OLAP cube. The OLAP cubes contain all data and the end user knows how to handle the cubes...

I think it's just a wish from my side...