topic Re: Question on OLAP reporting in SAS Enterprise Guide

Question on OLAP reporting

FrankBoekamp — Thu, 10 Nov 2011 14:50:57 GMT

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

admission_source

Ward

344

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

Cheers,

Frank

Question on OLAP reporting

FrankBoekamp — Wed, 30 Nov 2011 14:55:25 GMT

No reply :smileyconfused:

Does nobody else needs this?

Question on OLAP reporting

ChrisHemedinger — Wed, 30 Nov 2011 15:12:34 GMT

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

Question on OLAP reporting

FrankBoekamp — Wed, 30 Nov 2011 15:39:14 GMT

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

Question on OLAP reporting

Cynthia_sas — Wed, 30 Nov 2011 17:43:26 GMT

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

Re: Question on OLAP reporting

TomKari — Fri, 02 Dec 2011 03:37:20 GMT

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.)

Re: Question on OLAP reporting

LinusH — Fri, 02 Dec 2011 08:56:27 GMT

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

Re: Question on OLAP reporting

ChrisHemedinger — Fri, 02 Dec 2011 20:33:09 GMT

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

Re: Question on OLAP reporting

FrankBoekamp — Mon, 05 Dec 2011 09:08:39 GMT

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...