Hi all,

George Spofford, Siva Harinath, Francesco Civardi and I are currently
working on the second edition of the book 'MDX Solutions', which I'm sure
many of you on this newsgroup own. Our aims for this edition are to:
* Update the book to include SQL Server 2005's new MDX features, and
* Expand the content to include more detail on advanced MDX topics

As a result we're interested in hearing your opinions on the first edition:
what you liked and what you didn't like, mistakes that need correcting,
existing topics that need more detail and new topics that you think should be
included. The more specific you are the more it will help us - think of the
functions you've had trouble understanding (NonEmptyCrossjoin is on our
list!), the calculated members you've had trouble implementing and the
queries you've had trouble writing.

To give us your feedback please reply to this thread (the preferred option)
or email me directly. We're looking forward to hearing from you...

Thanks,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/

Hi Chris,

We're all all anxiously awating the updated edition - any projected
publication date?

An MDX performance topic that would be worth addressing arises out of
this entry in Mosa's blog - it appears that the best way to structure
aggregates across multiple dimensions may have changed from SQL 7 to 2K
(to 2005?):

http://sqljunkies.com/WebLog/mosha/a...2/13/7784.aspx

In the "MDX Solutions" book, at pages 69-70, George Spofford discusses
the task of computing expression [Units Sold] * [Last Update of Price]
at the leaf-level cells and then aggregating the result up to the higher
levels.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thanks for the feedback! MDX Scripting is something I'll be looking at, and
that post on Mosha's blog is on my list of things to explore further. Any
other ideas would be much appreciated...

I think at the moment we're aiming at publication around the same time as
Yukon is released, so possibly the end of this year.

Regards,

Chris
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Deepak Puri" wrote:

Hi Chris:

Great book, gets lots of use (abuse). I have two initial thoughts:

1) It seems a section that expands on the concept of a Periodicity
dimension or a Calculations dimension would be very worthwhile. When I
first read this section in the book (Chapter 11, pp228-229), I dog-eared
the pages thinking it seemed important but didn't understand its
significance until many months later. Now, as I'm re-reading the section
I'm finally beginning to eliminate the need to have some two-dozen YTD type
calculated measures in favor of a dimension that includes a YTD member.
(Understanding the importance of a Periodicity dimension didn't come to
light until a rep from ProClarity demo-ed their software... we didn't buy;
couldn't afford.) Tom Chester's website has a download example that also
illustrates the concept quite nicely:
(e.g.,
http://www.tomchester.net/articlesdo...dimension.html)

2) Something that is absent from just about every MDX article, book, and
download I've come across are cubes that maintain two Time Dimensions (not
hierachies): one time dimension that tracks the date something was billed,
and another time dimension that tracks when something was paid. I work in
health care where the majority of payments are made two or three months
after services have been rendered. Consequently, I analyze data according
to "runout": for example, services billed in Jan but paid in Feb vs billed
in Jan and paid through March. I would think that many businesses would
operate in a similar fashion, thus being able to budget for unpaid
services/purchases. Any chance you guys could be the first to actually
write about this?

Again, great book. Look forward to the next edition.
--Pete

Thanks for the feedback, Peter. Regarding your second scenario, can you
expand on how you've tried to solve your problem and why you need MDX? I
would have thought that if the granularity of your fact table was the
invoice, and you had two key columns for Date Billed and Date Paid, I would
have thought that any cube you built would automatically handle the kind of
queries you describe.

Chris
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Peter Zajkowski via SQLMonster.com" wrote:

Hi Chris,
I found the first book very interesting and useful and am always using it as
a reference. My hints are the following:
- I think it would be useful to have more practical examples (and more
complete) in the next edition, mainly in the "Functions and operators
References" chapter.
- I think the chapter 6 - "The many Ways to calculate in Analysis Services"
should be deeply updated, due to the strong difference of the AS2005.
Further, It would be useful to have something more about the Custom Member
Formula, Custom Rollup Formula, and also on how to use MDX in the "Script
panel" of the AS2005 Calculations tab of the cube, since it should be used
also to simplify the Calculation Passes (which is not very clear from the
book), if I correctly understood.
- I think it also would be very useful to deepen the "Client Programming
Basics" chapter, maybe with some references to AMO, if possible.
Have a good work.
Vania




"Chris Webb" wrote:

Hi Chris:

I finally have a moment to reply to your posting. The catch with having two
separate time elements (dimensions) is figuring out how to write the mdx
that reflects the intersection of these two dimensions, especially when
dealing with invoices.

For example, the following expression is useful in summing the total number
of units incurred up to a given month (YTD) where the units were paid for
up to a given month (YTD): e.g., incurred March YTD paid through June YTD --
this would sum all units incurred Jan, Feb, & March that were subsequently
paid anytime between Jan and June:

SUM(Crossjoin(Ytd([TIME_INCURRED]),{[TIME_PAID]
..CURRENTMEMBER.LEVEL.MEMBERS.ITEM(0):[TIME_PAID]}),[Measures].[UNITS])


Using parallel period as well, I can then determine how things looked the
previous year.

Again, nearly every example I've ever seen regarding the use of analysis
over time only incorporates a single time dimension, yet the reality of
getting paid vs. billed ought to be highlighted as well. It answers the
question "How long does it take for a company to get paid for services
rendered?"

Initially when I tried to write the mdx, I kept getting results that were
only the true intersection of the current time members. For example, if my
client tool (Excel pivot table) had March selected for Time_Incurred and
June selected for Time_Paid, then the sum of Units was only for those Units
that were incurred only in March and paid only in June. Not the results I
was looking for. Only through many trial and errors, and less-than-useful
responses from discussion forums, was I able to grasp the concept of using
a crossjoin and YTD function to achieve the desired results.

Since then, however, I've been able to gleam useful tips from the "OLAP
Solutions" book regarding a periodicity dimension so that I no longer need
measure-specific YTD calculations, but can now have a single YTD
calculation in a periodicity dimension to cut through all relevant measures.

Anyway, I hope this information is useful. Feel free to contact me with any
additional questions you may have.
--Peter