Hello!
I have an application that uses OWC 11. Interface of an application is a
quite simple: user selects a "report" (i.e. my custom MDX Query), press "Run
report" button and OWC 11 PivotTable is populated with data. Then, user
slice-n-dice PivotTable as it wants. This application have some limitations,
related to OWC 11 PivotTable, that was designed BEFORE SSAS'2005:
-Cannot arrange dimension attributes and measures into folders
-MANY bugs with calculated members in MDX (SSAS'2005) query (do not display
color correctly, this is only one example)
-VERY slow execution
-et cetera
On my questions in Microsoft's newsgroups I received a simple answer: OWC 11
is an obsolette technology, that works with SSAS'2005 incorrectly, you should
develop your application with VS'2005 Office Tools and write Excel'2007
add-in. I'm trying to follow this recommendations, but I found that I cannot
execute my custom MDX query in Excel'2007. Yes, I can go another way: I can
design my custom presentation in Excel'2007 PivotTable (except of writing
MDX), and store ready Excel'2007 file in my "reports storage" (except of
storing MDX queries). But I'm facing with another problems in this
front-line. For example, you may read another post I created in this
newsgroups:
http://msdn.microsoft.com/newsgroups...b-0c3315213d7b
Shortly, all my "reports" use calculated members. If I add calculated
members to PivotTable with Office 2007 VBA, they have incorrect number
format. Also, I cannot add color and font expressions for my calculated
members (while in custom MDX I can). At last, this solution have excess
complexity: I have to add calculated members with help of VBA and than I have
to design Pivot in Excel.
Now I've found a workaround:
-I declare ALL calculated members on the server-side (in BI studio, while
designing of my AS Solution)
-Design pivot report in Excel'2007 PivotTable
-Store Excel'2007 workbook with pivot report in database
When user requests any report, application load Excel'2007 workbook with
this report from database storage, open report and refresh it.
This solution DO NOW SATISFIES me in long term:
-Declaring server-side calculated members means that I cannot distribute my
cube separately from application. If I have several cube installations, and I
release new version of my BI-application, or simply add a new report, I have
to include in at a new cube design or execute some MDX-statement once during
setup, that add new calculated member on every installation. Of course, it's
quite hard to maintain this system.
-If I design reports in Excel instead of writing custom MDX queries, MDX
queries produced by Excel are not as effective as I can write in MDX myself,
because PivotTable is UNIVERSAL product, while my reports are special and do
not use all functionality simultaneously.
Also I'm confused with a lack of OLAP clients for SSAS'2005, provided by
Microsoft. As I understand, there are only 3 ways to display multidimensional
data using Microsoft products, each with it's own disadvantages:
1. Excel'2007. All my problems are written above. It is very impressive, but
too raw product for BI needs. Besides, I cannot embed it's PivotTable in my
application and distribute it separate from Office'2007, as I could do with
OWC. I cannot write my application in Borland Delphi, for example - I have to
use VS'2005 office tools and work in Office'2007 interface. It is a really
great disadvantage, in some cases.
2. Reporting Services 2005. It's my opinion, that as far as this technology
is amazing for relation database reporting, as it is miscarried for
multidimension analysis. Flattening MD-data into relation dataset and then
re-pivotting it in report - it look very funny for beginners, not for
enterprise-scale application. Also, I can slice my data with parameters, but
how can I drillthrough hierarchies, replace attributes on axes, etc?
SSRS'2005 do not know anything about my UDM features - this is why reporting
services provide me with so poor functionality.
3. Proclarity software. Proclarity is acquired by Microsoft, as I
understand. Your support said me last year, that I will be able to download
it's application from my MSDN Universal downloads site at July'2006... It is
still not available today. I just cannot download trial version of it's
software... :(
I'm totally surprised, that from year 2005, when SSAS'2005 (that is really
impressive and feature-rich OLAP engine) was released, I have not convenient
and functional technology to deliver analytical data to end-user for today.
Thank you
P.S. The article you provided is about OWC. It is obsolette together with
OWC :(