Hello Guys,

I'm trying to create offline local cube, from a source which is another
local cube. I'm using the infamous CREATE CUBE ... INSERT INTO ... SELECT
ADO syntax for the creation, but I have an issue with a hierarchy defined in
the source cube. I have the following hierarchy defined in 'Geography'
dimension:

LEVEL1 LEVEL 2
Geo1
Geo1 Geo2
Geo3

and I use the following statement to get my data in destination cube
'MyDestination':

PROVIDER=MSOLAP;
DATA SOURCE=C:\MyDestination.cub;
SOURCE_DSN="PROVIDER=MSOLAP;DATA SOURCE=C:\MySource.cub;INITIAL
CATALOG=MySource;";
CREATECUBE="CREATE CUBE [MyDestination](
DIMENSION [Geography] TYPE Geography,
LEVEL [Geography Name] TYPE ALL,
LEVEL [L1],
LEVEL [L2],
DIMENSION [Product] TYPE Products, LEVEL [Product Name],
DIMENSION [Time Period] TYPE Time, LEVEL [Time Period Name],
MEASURE [Dollars] Function Sum Format '#.#',
MEASURE [Units] Function Sum Format '#.#' )";
INSERTINTO=INSERT INTO [MyDestination](
[Geography].[L1],
[Geography].[L2],
[Product].[Product Name],
[Time Period].[Time Period Name],
Measures.[Dollars],
Measures.[Units] )
SELECT
[MySource].[Geography:L1],
[MySource].[Geography:L2],
[MySource].[Product:Product Name],
[MySource].[Time Period:Time Period Name],
[MySource].[Measuresollars],
[MySource].[Measures:Units]
FROM [MySource];

---------------------------------------

The upper statement executes fine, but the destination cube doesn't contain
'Geo3' member at all. I have tried to import data from 'MySource.cub' file
into SQL Server 2000 table and I get flattened recordset, which doesn't
include 'Geo3' member there either.

Can someone please tell me, whether described flattened recordset behaviour
is a bug or it is implemented by design? And if it is by design how can I
bring a hierarchy from a source cube into a local cube? Do I miss some
special undocumented option in the statement above, which can possibly help
me?

I have to say one more thing. I have tried definining source hierarchy both
ways - as normal and as parent-child, but it didn't make a difference in the
result 'MyDestination.cub' file.

Thank you very much for your time!

Ivan

Only members that have data in the fact table are displayed in a local cube.
As far as I know, that's the only reason why the Geo3 member would not show
up in the local cube. Browse the MySource server cube. If you see Geo3
displayed with no values for any of the measures you are including in the
local cube, then Geo3 will not appear in the local cube.

Tim Peterson
www.localcubetask.com

Hi Tim,

Both source and destination cubes are local. There are facts in MySource
cube for Geo3 and it appears fine in MDX Sample Application. The problem
appears when I try to create local cube out of another local cube. The
support for recordset flattening from local cube is miserable.

I just finished discussing this issue with MS support team (They were
top-notch. Thanks!) and it appears this functionality is not officially
supported. They also say there is licensing problem, too and it wouldn't be
fixed in the future unless they decide to change their policy.

Ivan

"Tim Peterson" <tpeterson (AT) sdgcomputing (DOT) com> wrote