Exposing Cubes to Users via MDX Reports 1

[GrandMauler]

This year, I managed to convince my bosses to invest in a BI platform. We purchased SQL 2k5, two high-end servers, and Proclarity Desktop proffesional.

I have built a Cube for one of our clients and showed them how to access it via an Excel OLAP client. I built a very sophisticated SSIS pkg to update and process the Cube.

I have turned one of the high-end servers into a Reporting Services server. I showed my bosses how we can use subscriptions to generate scheduled reports.

Problem:
I have recently got the hang of MDX and built a sophisticated MDX query that works wonderfully in Proclarity... BUT NOT IN THE BIDS REPORT DESIGNER. Apparently, the MDX editor in the Report Designer has some limitations... first of all, the first axis can only be measures. wow.

So my question is this: I have browsed various report generators that say they are well-suited for OLAP Reports (Data Dynamics, SharePoint, etc). Which one is the most popular out there specifically for complex MDX reports?
And is it integratable with SSRS?

(I have to assume that, whatever the solution, it has to be in rdl)


Thanks in advance!

 

[GrandMauler]

Ok, I think I panicked too soon.

I found a solution that I think will work:

http://danmeyers.spaces.live.com/blog/cns!7B729558749C22DD!334.entry

Basically, you have to use the OLE DB provider and write your own connection string.

This allows you to use any MDX query you want to.

So far, it looks promising.


However, please feel free to answer the above post. I DESPERATELY WELCOME ANY ADVICE ON MDX AND OLAP.

Cheers!

 

[Flybridge]

RSInteract (rsinteract.com)just got a decent write up in one of the SQL mags (can't remember which). You can download an eval copy.

'The world isn't round - it's bent!' Spike Milligan

 

[Flybridge]

Forgot to say, the review was slightly wrong (in case you find it) - RSinteract CAN read OLAP.

'The world isn't round - it's bent!' Spike Milligan

 

[GrandMauler]

Flybridge -

WOW! I'm very impressed with what I'm reading so far.

Have you guys purchased it?
If so, what strategy did you use to convince your company to make the investment?

From what I'm seeing, it sells itself... provided you know something about BI. However, I'm dealing with people who have only recently learned what BI stands for.

Thanks!

 

[MDXer]

why not publish your reports out using proclarity? If you have invested in Proclarity and SSRS is unable to deliver the content put PAS can then why invest in yet another tool?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[GrandMauler]

It's Proclarity Desktop Professional.


Is that all I need or do I need to purchase some thing else?

 

[MDXer]

Then I would look at getting PAS to deploy web based reports to. In my opinion one of the pitfalls to a BI implementation is is the precieved need to always bring in different tools. If you bring in another tool you are then at 3 tools for an implementation that sounds like it is still in development. BI implementations can often be seen as a blackhole, where money is continously being invested in it but no immendiate ROI is being seen.


In response to your OP you can infact use the OLDEDB for OLAP connection and enter an MDX query, however making it useable with parameters can me challenging. Pissibly revisiting the report design or approach may prove useful. WHile in SSRS data set measures are the only elements on collumn using a Matrix to display the data may allow you to implement the nesting you require.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[GrandMauler]

3 hours later, our semi-young hero makes a startling discovery... he needs Proclarity Analytics Server to publish his mdx reports....

----------------------------------------------------------

one of the pitfalls to a BI implementation is is the precieved need to always bring in different tools.

BI implementations can often be seen as a blackhole, where money is continously being invested in it but no immendiate ROI is being seen.

Never truer words were spoken.

Possibly revisiting the report design or approach may prove useful. WHile in SSRS data set measures are the only elements on collumn using a Matrix to display the data may allow you to implement the nesting you require.

Yes, this seems the only alternative left to me, if for no other reason than to prove why we need a Proclarity Analytics Server or RSInteract... although RSInteract seems like a much cheaper solution than PAS.

I'm thinking of using some sort SSIS script that modifies the Report's XML, rdl, or MDX script each week, so that the Current Week numbers are actually... the current week's.

Any suggestion on the best way to go about doing this?
The first part is easy: I simply write either a parametric SQL or VB script that generates MDX script for the current week.

The next part is what I don't know: How do I then switch the new MDX script with the old, then redeploy?

Actually, even the redeploy should be easy with SSIS. It's the swiching MDX thing I need to know.

Anyway, any insight would be appreciated.


Cheers!

 

[MDXer]

You can create a member within you time or date dimension that will generate the most current period I typically accomplish this by only having my time dimension built out to include the most current data loaded then a TAIL() on the the time dim will allow you to return the last member within that level.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[GrandMauler]

Thank you!