Does anyone know whether Excel 2008 can connect to OLAP cubes? (Or
where else I could find an answer to this question?) I emailed one of
the writers of the MS Mac BU blog, but never got a response.
thanks,
Meredith
Hi,
On the plus side I do not know! I have not seen a copy of 2008. However, I
can tell you that in 2007 (the PC version), Microsoft has introduced 5 new
functions which allow you to access CUBE file data.
Here is what the 2007 help system says:
CUBESET - This function will fetch the set that is defined by the
set_expression parameter. Optional parameters allow you to specify the
ordering of the set as well as the caption to be displayed in the Excel cell
that contains this formula. (Note that the set itself won’t have a display
value.) For example, the formula: =CUBESET ("Adventure
Works","[Customer].[Customer Geography].[All
Customers].children","Countries") returns the set of countries in the
Customer Geography hierarchy and shows “Countries†as the cell’s display
value.
CUBEVALUE - This function will fetch the aggregated value from the cube
filtered by the various member_expression arguments. For example, the
formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross
Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")
returns the value $5,035,271.22 which is the aggregated amount in the
Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.
CUBEKPIMEMBER - This function returns a KPI (Key Performance Indicator) from
the OLAP cube.
CUBEMEMBER - This function will fetch the member or tuple defined by the
member_expression. For example, (from the illustration above,) the formula:
=CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns the
member named “On Promotion†from the “Sales Reason†dimension of the
Adventure Works cube.
CUBEMEMBERPROPERTY - This function returns a property of a member in the
OLAP cube.
CUBERANKEDMEMBER - This function returns the Nth item from a set. This can
be very useful when building a Top N (or Bottom N) report in Excel.
CUBESETCOUNT - This function returns the number of items in a set.
Typically the argument to this function will be a CUBESET function or a
reference to a CUBESET function.
Again, I have not seen the 2008 version of Excel so I can't say for sure if
the Mac gets these functions, but if history holds try the odds are good.
I understand there will be a full day presentation at Mac World on Jan 14
covering what's new.
--
Cheers,
Shane Devenshire
meredithalix said:Does anyone know whether Excel 2008 can connect to OLAP cubes? (Or
where else I could find an answer to this question?) I emailed one of
the writers of the MS Mac BU blog, but never got a response.
thanks,
Meredith
Hi Shane and Meredith,
Now that MacWorld is over and the product is on the market I can answer this
question. The good news is in Excel 2008 the Help is dramatically improved.
The answer to your question lies therein (I'll paste the result below).
The bad news is that Excel for Mac still does not support CUBE functions. I
have more comments after the paste - here's the story pasted from Excel 2008
help:
+_+_+
Cause: CUBE functions are not supported in versions of Excel for Mac.
Solution: Open the workbook in a version of Excel that supports CUBE
functions.
Excel 2007 and Excel 2003 support the following CUBE functions:
CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET,
CUBESETCOUNT, and CUBEVALUE.
Cause: The BAHTTEXT function is not supported in versions of Excel for Mac.
Solution: Prevent sheet calculation errors by converting cells that
contain the BAHTTEXT function to values.
The BAHTTEXT function is used by Excel for Windows to convert a number to
Thai text and add a suffix of "Baht." Excel for Mac can open and display
sheets created with Excel for Windows that contain BAHTTEXT, but if you
recalculate such a sheet in Excel for Mac, any cells containing BAHTTEXT
will display the #NAME! error value. To prevent Excel from recalculating a
cell containing BAHTTEXT, copy the cell and then paste it back into place as
a value by using the Paste Special command on the Edit menu. After you do
this, the cell does not recalculate with the rest of the workbook.
Cause: The CALL, REGISTER.ID, RTD, and SQL.REQUEST functions are not
supported in versions of Excel for Mac.
Solution: Open the workbook in a version of Excel that supports these
functions.
Versions of Excel for Windows support the CALL, REGISTER.ID, RTD, and
SQL.REQUEST functions.
Solution: Use Microsoft Query to connect to and query a SQL database.
To learn more, see Import data from a database.
Cause: The EUROCONVERT function is not supported in Excel 2008 for Mac.
Solution: Open the workbook in a version of Excel that supports the
EUROCONVERT functions.
Excel 2004 for Mac supports the EUROCONVERT function.
Solution: Change the currency symbol to the euro sign (EURO).
+_+_+
The next question that pops up is, "why not?" The reason is that CUBE
functions are barely used on the PC version. MacBU looks askance and wonders
why anyone would use CUBE functions on a Mac? How many Mac users would use
this feature considering Windows users barely use it? Why should we put our
scarce resources building this functionality into Mac Excel when we can do
other stuff we think our customers want instead?
So your homework is to write a business case for MacBU that justifies why
MacBU ought to be cross-platform compatible with CUBE functions and then
send it to Microsoft at this URL:http://www.microsoft.com/mac/suggestions.mspx?product=excel
One or two feedbacks won't get your request filled, but over the years I've
seen several comments about the lack of CUBE functionality on the Mac. If
you know of a larger community that uses CUBE and wants it on the Mac, let
the community know that now is the time to express their business cases to
MacBU at the above URL.
Thanks.
-Jim
Quoting from "ShaneDevenshire" <[email protected]>,
On the plus side I do not know! I have not seen a copy of 2008. However, I
can tell you that in 2007 (the PC version), Microsoft has introduced 5 new
functions which allow you to access CUBE file data.Here is what the 2007 help system says:CUBESET - This function will fetch the set that is defined by the
set_expression parameter. Optional parameters allow you to specify the
ordering of the set as well as the caption to be displayed in the Excel cell
that contains this formula. (Note that the set itself won't have a display
value.) For example, the formula: =CUBESET ("Adventure
Works","[Customer].[Customer Geography].[All
Customers].children","Countries") returns the set of countries in the
Customer Geography hierarchy and shows "Countries" as the cell's display
value.
CUBEVALUE - This function will fetch the aggregated value from the cube
filtered by the various member_expression arguments. For example, the
formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross
Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")
returns the value $5,035,271.22 which is the aggregated amount in the
Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.
CUBEKPIMEMBER - This function returns a KPI (Key Performance Indicator) from
the OLAP cube.
CUBEMEMBER - This function will fetch the member or tuple defined by the
member_expression. For example, (from the illustration above,) the formula:
=CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns the
member named "On Promotion" from the "Sales Reason" dimension of the
Adventure Works cube.
CUBEMEMBERPROPERTY - This function returns a property of a member in the
OLAP cube.
CUBERANKEDMEMBER - This function returns the Nth item from a set. This can
be very useful when building a Top N (or Bottom N) report in Excel.
CUBESETCOUNT - This function returns the number of items in a set.
Typically the argument to this function will be a CUBESET function or a
reference to a CUBESET function.Again, I have not seen the 2008 version of Excel so I can't say for sure if
the Mac gets these functions, but if history holds try the odds are good.I understand there will be a full day presentation at Mac World on Jan 14
covering what's new."meredithalix" wrote:
--
Jim Gordon
Mac MVP
MVPs are not Microsoft Employees
MVP infohttp://mvp.support.microsoft.com/
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.