CUBE Functions 3: Formula AutoComplete revisited

re: CUBE Functions 3: Formula AutoComplete revisited

Kory — Sat, 11 Feb 2006 00:23:11 GMT

You might cover this (or already have): Will Excel 12 allow submitting a direct MDX statement? How about support for Actions (including DrillThrough)?

Thanks

Kory

re: CUBE Functions 3: Formula AutoComplete revisited

David Gainer — Sat, 11 Feb 2006 06:36:22 GMT

Hi Kory

Yes to actions, no to direct MDX statement. See previous post for full support of Analysis Services.

re: CUBE Functions 3: Formula AutoComplete revisited

Harlan Grove — Mon, 13 Feb 2006 04:34:41 GMT

Has anyone noticed that when the topic involves Excel and servers the number of comments drops considerably compared to general spreadsheet functionality? Could this be a sign that Microsoft has found a new 80/20 rule: 80% of development effort spent adding features that in blissfully optimistic estimates may be used by at most 20% of users?

re: CUBE Functions 3: Formula AutoComplete revisited

Paul — Mon, 13 Feb 2006 05:25:49 GMT

Harlan is right, historically, but I think there is a lean towards advanced usage of Excel in the mainstream now.

re: CUBE Functions 3: Formula AutoComplete revisited

Jonathan Cooper — Mon, 13 Feb 2006 18:05:06 GMT

I think that Excel has become so popular, that the general knowledge base and usage of Excel will continue to escalate towards the more advanced functions.

With this particular post, I can not get the screen shots to show. Anyone else having this same problem?

re: CUBE Functions 3: Formula AutoComplete revisited

John Greenan — Tue, 14 Feb 2006 20:34:37 GMT

Any chance of some info on what's going on with VBA, COM AddIns and so on???

re: CUBE Functions 3: Formula AutoComplete revisited

Harlan Grove — Tue, 14 Feb 2006 22:42:14 GMT

Jonathan Cooper wrote...
>I think that Excel has become so popular, that the general
>knowledge base and usage of Excel will continue to escalate
>towards the more advanced functions.

Begging the question whether this is a good thing or not. I agree that Excel is getting more use, but it's either used by programmers who don't really understand spreadsheet design or by nonprogrammers who don't understand software engineering at all. The usual result is bloated workbooks that are already a nightmare to maintain. Excel 12 will boost that nightmare a few quanta.

re: CUBE Functions 3: Formula AutoComplete revisited

David Gainer — Tue, 14 Feb 2006 23:17:46 GMT

John,

There has not been significant change in the VBA IDE. We have added all the new capabilities to Excel's OM.

At a later point, I will have some other programmability posts.

re: CUBE Functions 3: Formula AutoComplete revisited

Damon Longworth — Wed, 15 Feb 2006 08:04:06 GMT

Jonathan and Harlan

Both points are valid, although I must take exception with Harlan's generic categorization of spreadsheet users.

There are many users who fall into both categories, but there is a growing community of Excel developers who are aware of Excels capabilities and constraints. I too, share your concern about bloated files with Excel 12 and some of our users.

In my opinion, we need to promote Excel as a development platform and educate the user community. They need to know about efficient spreadsheet design, along with the appropriate applications for Excel.

Damon Longworth
www.ExcelUserConference.com

re: CUBE Functions 3: Formula AutoComplete revisited

Harlan Grove — Thu, 16 Feb 2006 00:14:01 GMT

Damon Longworth...
...
>In my opinion, we need to promote Excel as a
>development platform and educate the user
>community. They need to know about efficient
>spreadsheet design, along with the appropriate
>applications for Excel.

What's an appropriate application for Excel?

For ad hoc financial or what-if (aka decision support) analysis, Excel is as good as APL and its offspring, but that's the sort of application for which development is pointless since it's next to impossible to predict all the variations.

For repetitive tasks, there are usually better systems. Report generation is usually better done in Access than Excel. Some may misperceive the oh, so wonderful new rainbow effects available in Excel 12 as a benefit. Most of the people who use them will succeed in reaching new levels of ugliness.

For formal analysis, even exploratory data analysis, most stats packages are better than Excel. Since it appears the DATP isn't up for revision beyond stripping off its udfs, it'll still provide only lightweight functionality.

As for the user community, what do you believe it is? Two groups: users and developers? Users being the people who 'use' Excel only insofar as they need it to run the models built by the developers? And these developers, who are presumably familiar with web services, managed coding, etc., will have how much experience *using* spreadsheets in order to inform their design decisions?

I work for a company that employs hundreds of developers of all sorts and a few dozen of whom work with Excel and FormulaOne. Without exception, the in-house systems that avoid using Excel or FormulaOne are easier to use, faster, and less crash-prone. Why's that? Maybe the 'Excel developers' are the less competent ones. That's not going to change any time soon. Also, Excel's event handling is crude.

Finally, if Excel is really meant to be used for REAL APPLICATION DEVELOPMENT, it's time to provide much greater granularity in recalculation (different calc settings, auto vs manual, in each open workbook at least), simultaneous background execution of macros in one workbook and interactive use of other open workbooks (this can be kludged using multiple Excel instances), and augment workspaces so that they can have VBA modules and their own events that could apply across all open workbooks in the same way, e.g., SheetChange applies across all worksheets in a workbook.

Why stop there. Until Excel provides true 3D referencing, like @INDEX(A:A1..D:X5,1,2,3) to refer to cell C:B3 as provided by 123 as well as relative worksheet referencing, most models built using Excel will involve lots of kludges.

The reason there's so much traffic in the Excel newsgroups isn't that there are thousands of Excel users pushing the development envelop. Read the postings. It's because most Excel users are novices building their own ad hoc models or (mis)using Excel to generate reports, and few seem to be able to read online help. This is your future developer base. And you don't see any problems?

I may have a distorted view. To me spreadsheets are tools for performing calculations, a branch of applied math. Spreadsheet formulas are not the ideal means of expressing mathematical relationships, especially not for recursive relationships that can't be expressed as array formulas. Procedural languages may be no better, but there are other alternatives. Lotus Improv in the past and Quantrix currently provide much better means of expressing relationships. MDX may also. And they provide true multidimensionality.

Spreadsheets generally and Excel in particular have been widely and throughly misused over the last two decades. Excel 12 will exascerbate the problem. You may respond that it's the user's choice, but that choice is all too often made out of ignorance of alternatives. Excel 12 will make the situation worse.

When things go wrong (and they will), I'd prefer going through thousands of lines of structured procedural code than thousands of cell formulas.

re: CUBE Functions 3: Formula AutoComplete revisited

David Gainer — Thu, 16 Feb 2006 03:25:54 GMT

Hi Kory (again)

I'd like to further clarify my answer to this question: "Will Excel 12 allow submitting a direct MDX statement?"

We don't support sending an MDX query such as: SELECT {[Customer].[Customer Geography].children} ON 0 FROM [Adventure Works]

We do however support arbitrary MDX expressions within the CUBESET function as long as those expressions resolve to a set. For example, when we say: =CUBESET("Adventure Works", "[Customer].[Customer Geography].children", "Countries") we have used this MDX expression as the second argument: [Customer].[Customer Geography].children

In a similar fashion, any MDX expression that resolves to a member can be used as an argument to the CUBEMEMBER function.

So we do support sending MDX expressions to Analysis Services for evaluation, but we don't support arbitrary MDX queries.

I hope this clarifies what was meant by my previous response to this question.

Microsoft Excel : CUBE Functions 3: Formula AutoComplete revisited

Dating — Sat, 31 May 2008 12:03:00 GMT

Previously when discussing CUBE functions, I showed a couple of examples of reports based on OLAP data that could be built using CUBE functions. Now I’d like to explain how we’ve done something very special with Formula AutoComplete to make it easy t

Microsoft Excel : CUBE Functions 3: Formula AutoComplete revisited

Weddings — Thu, 05 Jun 2008 22:43:42 GMT