Free Microsoft Excel 2013 Quick Reference

Creating an offline OLAP cube

HELP! So I try to create an offline OLAP cube using the New Database Query
dialog with its options. When I go to select a data source, it only gives me
the option of ODBC cataloged databases (I am trying to get to DB2).
Interestingly enough, when I just use Import Data it gives the the OLE DB
route. Anyway. So I get the data and I can see it, but when I select the
option to build an OLAP cube it crashes and gives me "Format of the
initialization string does not conform to the OLE DB specification" and
crashes the OLAP building part (well errors) but will return the data. Now,
the New Database Query uses ODBC from what I can tell then it seems to pass
to the cube builder via OLE DB and this is where it falls apart? Hooking this
data in SQL Server works fine - -but that is not an option for my team. Help?


Using Excel MS Query I've created an offline OLAP cube that will be on a
shared server. The data source for the cube was a 150,000 record Access
database. I have built a pivot table using the offline cube as the external
data source. Performance is fantastic - even from Europe!

My question is how many concurrent users can an offline OLAP data cube
safely handle? I've read somewhere that, unlike Analysis Server cubes, this
is not a true client-server model, so file-sharing errors may occasionally
result when multiple clients concurrently access the same cube. Have any of
you had experience using this approach? If so, did you have the
aforementioned problem? How many clients/users can this model safely support?

Thanks in advance,

Fred M3

I have created a pivot table with drill down functionality based on an excel olap cube. Every time I drill down however, each member of that dimension subtotals itself, even with the field subtotal option ticked off it will revert to automatic if the dimension is drilled back up.

I have a piece of code that removes the subtotals but it needs running each time you drill down into the dimension.

I suspect that because it is a calculated field this is not possible - anyone know something more obviously easy?

Thanks
Steve

Hi,
I created an OLAP cube by data->pivot table report->... from a relational
database. Then I chose "Create offline OLAP cube". The cube was created, but
it doesn't seem possible to add a formula based on the fact fields to the
OLAP cube.

The fact are 'sales' and 'revenues' and I need to see the ratio '=sales /
revenues'. Is it possible at all?? If not - can I buy any update or smth. to
get this functionality? This is a normal functionality for pivot tables. I
don't understand why can I not do this in OLAP cube?

Thanks for your time.
J

I have created a Pivot table which has an External datasource OLAP
cube( created by 'create offline OLAP cube wizard' by Excel from MS
Access database ).
I distribute my report comprising of An Excel workbook and olap cube
vy zipping the two files together.
When the other users unzip the file to see the report the Pivot asks
for the datasource again which they have to locate and then save.Is
there any way where can I define the path of the datasource same as
the path of the workbook?

When I create a Pivot table from OLAP cube once I click any page field
for the first time I get an error message "The Operation connects to
an external data source'
How can I suppress this warning message?

Hi,
I have very limited experience with OLAP, but I have manage to create an offline cube and 3-5 Pivot Tables from one OLAP cube (in the same workbook), however when I update my cube with new data, only one Pivot Table updates and others get an error message saying that cube can not be open for writing.
I understand that there might be limitations on how many concurrent connections can one cube have, my question is there any way around it? maybe using VBA code to close a connection?

Thanks for your help..

Hi,
I created an OLAP cube by data->pivot table report->... from a relational
database. Then I chose "Create offline OLAP cube". The cube was created, but
it doesn't seem possible to add a formula based on the fact fields to the
OLAP cube.

The fact are 'sales' and 'revenues' and I need to see the ratio '=sales /
revenues'. Is it possible at all?? If not - can I buy any update or smth. to
get this functionality? This is a normal functionality for pivot tables. I
don't understand why can I not do this in OLAP cube?

Thanks for your time.
J

I'm working with an existing workbook developed by someone else.

The pivot table has calulations associated along side it.
I want to include these calculations (none of the default calculations from field settings will do) within the pivot table...but I see from another thread that this cannot be done because it needs to be done from within the OLAP cube.

I know that this Pivot table is using OLAP cubes, but I am very new to OLAP cubes and while I can create a new OLAP cube, I'm trying to look for the existing one. I am probably wrong looking for an existing one as the pivot table is workiing fine but I don't see any under the OLAP cubes tab.

Any help is greatly appreciated

Hello folks,

I have set up a workbook with Pivots which read data from an offline OLAP. The workbook also has a separate worksheet with an external data query. Both the the OLAP and the external data query refresh from the same ACCESS database.

What I am trying to do is to programmatically refresh the external data query without refreshing the OLAP cube. For some reason, excel tries to refresh both although I refresh just the external data query.

I get the annoying message box "The cube file cannot be opened for writing"

The code I am using is as below (Excel 2002) and is activated by button click

Sub Button6_Click()
Sheets("Raw Data").Select

Selection.QueryTable.Refresh BackgroundQuery:=False

End Sub

Any ideas?

I am trying to build relative references to an SSAS OLAP cube using the GETPIVOTDATA function (excel 2007). There is a field in the pivot table named "Item". I have grouped this field and named it "Item1". I renamed the values in the grouped field so they are more descriptive than "Group1", etc. For example, I renamed "Group6" to "NBD".

When I create the initial formula by typing "=", arrowing onto a field in the cube and pressing enter, I get the expected result and a formula such as this:

=GETPIVOTDATA("[Measures].[Quantity]",$A$9,"[Item].[Item]","[Item].[Item].[Item1].[GROUPMEMBER.[ProductXl_Grp_6]].[Item]].[Item]].[All]]]")

This formula references Group6, aka "NBD". Excel seems to default the GETPIVOTDATA reference to some system name for the value in the grouped field ("ProductXL_Grp_6") instead of my renamed value. Is there a way for the formula to reference my renamed value instead of the system value for the grouped field? This would make it easier to build relative references and scale the formula to the entire worksheet.

Thanks

I am learning CUBE functions. The only one I can't get to perform is CUBESET.

Here's my scenario:

I've constructed an offline OLAP Cube using Access Northwind (as explained
in http://wang.se/en/CreateOLAPCube.html). Columns I am using are
CompanyName, Country, Quantity, Subtotal, OrderDate, ProductName,
CategoryName.

My OLAP Cube Dimensions a
Customer -- Country -- CompanyName
Product -- CategoryName -- ProductName
OrderDate -- Year -- Quarter -- Month -- Week -- Date

Next I've read articles in MSDN Blogs on CUBE Functions and followed the
examples in CUBE Functions 2: A Few Examples.

The CUBEMEMBER functions and the CUBEVALUE functions perform fine. But I
have yet to be able to construct anything using CUBESET to create a set of
All Products Sold.

Anybody out there who can guide me to the winning combination of terms in
the SET_EXPRESSION that I need to have a working CUBESET?

I'll be eternally grateful to anyone who can deciper this.

I have an excel workbook that has a number of hyperlinks to files that
are on file systems external to my laptop. These are mostly PowerPoint
slides that reside on network shares. I also have a fair number of
hyperlinks that point to web pages.

I want to be able to make a copy of this workbook and run some VB
against the copy, that will copy all the targets of the hyperlinks to a
local directory on my laptop, and then update the hyperlinks to reflect
the new locations. In this way I could create an "off-line" copy of
my workbook.

How difficult or easy would this be?

Also, how about the hyperlinks to webpages. How would I cache these? Or
would i be beter off copying these as well?

Ex.

Where hyperlink=/some-mount-point/some-directory-path/some-file.ppt
I want to copy /some-mount-point/some-directory-path/some-file.ppt
To /some-local-directory/some-file.ppt
Update hyperlink=/some-local-directory/some-file.ppt

-barton

Hi there,

is there any way to change the visibility - state of PivotItems AT
ONCE ?
With an "Offline OLAP - Cube" this is possible.

I'would like to know, if there's some funcionality for "PivotTables",
too.

I present some SAP - Data within this pivottable and want to give the
users the possibility to change product groups. These groups control,
what PivotItems are visible and what aren't.

By now, i have to refresh row by row. So i fear my users will suffer
of
epilepsy, since the screen is flickering horrendously.

With Olap cubes, i had to:

ActiveSheet.PivotTables("PivotTable1").CubeFields( 1).TreeviewControl.Hidden
= _
Array(new_items_helper, new_items_cube_list)

and the update process went by as smooth as opening a blank word
document!

Is there a TreeViewControl for the PivotFields, too, that allows one
to set the state of multiple PivotItems at once ?

Greetings and excuse this bad english,
Ole V.-M.

All - I'm trying to create a quick "proof of concept" whereby a spreadsheet queries a database and returns summaries of sales data. Normally I'd do this by opening the most recent data then summarizing in VBA or via some other formulaic method but I'd like to use an OLAP cube in this case since the person I'm building it for uses Excel 2000.

My understanding is that you can build an OLAP cube in Excel 2003 as a default option when importing a database via the query manager. However I don't appear to have a corresponding option in 2007. Does anyone know if this feature still exists? Excel help is next to useless as far as I can see on this.

See here for how to in Excel 2002 - http://office.microsoft.com/en-us/he...127121033.aspx

Thanks in advance,
John

Lets say you have to plan capacities for two Teams per Week over two months.
For each week you have got target values and actual values in hours (h).
A table can look like this in 2008 for week 27 and 28:

Week 27:
Team A, actual 3h, target 2h
Team B, actual 4h, target 1h

Week 28:
Team A, actual 2h, target 2h
Team B, actual 2h, target 1h

Actual and Target are Measures in the cube. Lets build a chart that displays
the actual as bars and the target as points.
The * is the point for target/Team A and O is the point for target/Team B
The chart looks like this in beautiful ASCII:

4 -+- +----+
| | B |
3 -+- +----+ |
| | A | |
2 -+- | | | * +----+----+ *
| | | | | A | B |
1 -+- | | | O | | | O
| | | | | | |
0 -+---+----+----+-------------------+----+----+-------------
Year 2008
Week 27 Week 28

A = Team A
B = Team B
* = target/Team A
O = target/Team B

Now the QUESTION/PROBLEM I have got:
I want to Sum target/Team A and target/Team B in ONE Value that is called
"Overall target". But because I am using Data from an OLAP-Cube I cant Modify
data or the data-range. So it seems the calculated sum must be delivered from
the sube.
But the Measure "target value" is alway split up for Team A and Team B
separately and I cant stack it up.
Even if I create a variable in the Cube that sums up the target values for
the teams it gets split up in the chart.

The goal is that the chart looks like this:
4 -+- +----+
| | A |
3 -+- +----+ | @ @
| | | |
2 -+- | | | +----+----+
| | | | | A | B |
1 -+- | | | | | |
| | | | | | |
0 -+---+----+----+----------------- +----+----+-------------
Year 2008
Week 27 Week 28

A = Team A
B = Team B
@ = Overall target (so @ = * + O respectively 3 = 2 + 1

A long Question I know, but I would really be glad if you could help me out!

I need to determine a price based on approximately 700 combinations of
choices. Basically, I have a list of 7 days of arrival (Mon-Sun), a list of
15 length-of-hours worked (from 8 hours over 1 day to 80 hours over 12 days),
and a list of 7 days of departure (Mon-Sun). The User chooses one from each
list in ANY combination (7 x 15 x 7) ... that's 735 combinations. I can (try
to) figure out how an OLAP cube or a Pivot Table works ... but I don't want
to waste my time if it's not going to solve my problem to begin with. So, do
you know if I can create some kind of cube/table/something so that I
determine a price, based on any combination of the three lists? (By the way,
I use the Data Validation List in each of the three cells.) Thanks a lot.

Hi,

I'm pulling data into excel from an OLAP cube, using the following code:

(in cell A1)

	VB:
	
=CUBESET("my OLAP cube connection string", [Products].[Group].[Division],"this_is_a_CubeSet",2,"[Measures].[Parts Used
Count]") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then list the results of that query using:


	VB:
	
=CUBERANKEDMEMBER(",A1,ROW(B1)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
... and repeat that downwards, which gives me a long list of items. Works great.

But instead of manually listing that CUBERANKEDMEMBER statement down a sheet - I would like a dropdownlist to automatically and dynamically show the results instead.

Any idea on how to do that. I'd like to avoid VB if possible, but have a feeling that might be needed here...

Thanks!

Hello,

This is an issue that has been dogging me for a long time now and causing significant distress.

I maintain a number of models requiring use of multiple OLAP Cubes in the same Excel workbook. I'm now using Excel 2007.

While I don't get this problem in Excel 2003, in Excel 2007 the Cubes often lose their connection to the source file. I'm given the error:

"Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."

When I attempt to recreate the connection, or select a new connection pointing to the same OLAP cube I'll often get the same error, but not always. I've tried using a separate connection, pointing to the same OLAP Cube and this also does not solve the problem.

Does anyone know how I can resolve this issue? Or do I just need to move my models back into Excel 2003?

Thanks in advance for any help.

Hi all,

I am trying to get my head around the benefits of OLAP! At present for our metrics analysis, our Information team populate huge unwieldy excel spreadsheets. The overall spreadsheet for the year to date (5 months worth of data) is already 110mb in size (due to the several pivottables and their associated caches) and hugely difficult to do anything with without excel dying from lack of memory

It appears to me that it would be better to put the data in an Access DB and then to create pivottables from an OLAP cube.

It appears to me that I can create a single pivottable in a workbook and using MSQuery make an OLAP cube. I can then make more pivottables as needed based on that OLAP cube.

The big question I have is whether I can update that cube when data is added to the access database and have the worksheets refresh the data without each one having to refresh the OLAP cube (which is what is happening at the moment). Presumably once the cube is refreshed, the other workbooks should be able to look at it and show the up to date data.

Or have I just got the wrong end of the stick?

Iain

I have created an OLAP cube based on my access database and have saved it into a network drive. I've set the options of the report so that it asks you if you want to refresh the cube (from the query to the access database) every time you open it. While I can refresh it fine, when other users in the network try to open the Cube and refresh it, I get the following error: Error while processing a partition. Does anybody know why this happens? Thanks so much. Diego

I'm trying to show % change from year-to-year, with years in columns in a pivot table. The source is a .cub file. To show % change, I'd need a calculated item or row, but you can't create that from a pivot table based on an OLAP database. Since my first approach failed, instead I created a pivot table of the same data from SQL server. With this approach, I was able to create calculated items that show the percent change from column to column. However, to also show the total in a row, I have to enable "grand total" -- which generates a very inaccurate % change in that grand total row...and now I'm struck writing a note on the report to "disregard the growth rates in the grand total row"--not a great solution. Also, I notice the pivot table generated from SQL server has lost all knowledge of how dimensions relate to each other (for example, that certain countries belong in a particular region)--and if I could figure out how to make this pivot table not display zero rows, that intelligence would return, as there is not data in meaningless combinations--but I can't figure out how to eliminate rows with no data, and that feature seems to be grayed out in the place I can get it to show up.
In short, I can't produce a decent presentation of data using either a pivot table connected to an OLAP cube, or one with the data fully included in the Excel file. And I don't know exactly which gremlins I'm fighting here...thanks for any help.

Whenever I try to create OLAP Cube the wizard will popup with tiny tiny
screen and can't see the wizard screen.

What do I do?

I am still relatively new to OLAP and just trying to get a better
understanding. If I have created an OLAP cube from my Access Database and it
is pulling into a PivotTable in Excel, is it possible to "pass" values back
to the Database without any additional programming or is just a one-way feed?

Secondly, does the information have to come back in a pivottable? I am
trying to create some reports and I seem to be very restricted with
pivottables.

Help! I need to create and OLAP cube using data from ms access. when i go
through creating a ms query using the wizard, the option i need is not
highlighted.
How do I activate this?

I am still relatively new to OLAP and just trying to get a better
understanding. If I have created an OLAP cube from my Access Database and it
is pulling into a PivotTable in Excel, is it possible to "pass" values back
to the Database without any additional programming or is just a one-way feed?

Secondly, does the information have to come back in a pivottable? I am
trying to create some reports and I seem to be very restricted with
pivottables.