AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!
We used the Profiler before and found the xmla-statement that is used to create the new cube ... no problem so far; it creates a new database with the cube as we want it to be.
But we didn't find the xmla-statement that writes the cube into the .cub file.
There are 3 lines in the Profiler where the filename of the .cub appears: 2 times in the context of a copy of the original CREATE GLOBAL CUBE statement and the last time in the line where we found the statement that creates the cube.
The Profiler says something like this:
"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"
The <batch> we can extract to create our cube, but the part before is no scripting-language we know.
Perhaps it makes sense for you? ;)
Greetings,
Sven
|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: Replacing the sole data source for existing cube?

Does BI Dev Studio support (and if so, how) replacing the sole data source for an existing cube with a new data source? As a note, this new data source is really an enhanced version of the original, but with added fields, stored procedures, etc. I imagine that, if I can do what I'm asking about above, then the DSV Refresh function will either automate, or at least document, the changes needed at DSV level to keep the cube connection valid.

Question restated: DoesBI Dev Studio support (and if so, how) replacing the sole data source for an existing AS2005 cube with a new data source?

The best way to change the source of data for an existing OLAP or Data Mining object is simply to change the connection string of the Data Source object on which that object depends.After doing this, you may want to refresh the Data Source View object to confirm that the new relational source contains equivalent tables. (Right-click on the background of the DSV and choose the Refresh menu item.)

The connection string of Data Source objects is configuration dependent.This is handy as it allows you to have a single Data Source that will use one connection string when your project is in “Test” configuration and another connection string when it is in “Production” configuration. Click on the Properties menu item in the Project menu to see the active configuration and use the “Configuration Manager” button to create new configurations.

It also is possible to change the primary Data Source of a Data Source View by setting the Data Source property in the property panel.(Edit the DSV and click the background of the diagram to ensure that the DSV itself is selected.)However, changing the primary Data Source only changes the default Data Source for new tables added to the DSV.Since Data Source Views support tables from multiple Data Sources, each table already included in the DSV is associated with a specific Data Source.To change the Data Source for tables already in the DSV, you need to is replace each of the tables with an equivalent table from the Data Source of your choice (Right click on a table and choose Replace Table->With Other Table…).

AS2005: MemberUniqueNameStyle = NamePath

Hi,

After having performance problems we implemented the Attribute Relationship in the cubes, but 99% of the hierarchies do not have a unique key for the levels. The suggestion from MS was to create the key as a collection from the parents to the level itself. Isn't it equivalent to MemberUniqueNameStyle = NamePath?

MemberUniqueNameStyle = NamePath (Analysis Services generates a compound name consisting of the name of each level and the caption of the member.) http://msdn2.microsoft.com/en-us/library/ms174900(SQL.90).aspx.

Thanks.

No, MemberUniqueNameStyle simply controls how Analysis Services generates the unique names of members - it doesn't affect the members or attribute relationships themselves. By using composite keys for your attributes, however, you will see changes in unique names generated using the default key-based algorithm.

HTH,

Chris

AS2005: Building a parent-child hierarchy on a table that also includes a surrogate key

Hi,

Any assistance with this will be most useful as I'm struggling to find a difinitive answer anywhere.

I have a table that includes a Surrogate Key column (primary key), a Child ID column, a Parent ID column and a Description column.

When building a parent-child dimension through the Analysis Services 2005 wizard the first decision comes on the 'Select the Main Dimension Table' screen. The Surrogate Key has to be selected as the 'Key Column' (for the relationship to the Fact Table) and the Description column is selected for the 'Column containing the member name (optional)'.

The next screen is 'Select Dimension Attributes' - do I select Child ID, Parent ID or both? And do I need to make any changes to the 'Attribute Key Column' and 'Attribute Name Column' fields in here (I cannot see why you would need to)?

Finally, the 'Define Parent-Child Relationship' screen highlights the issues around selecting the Surrogate Key as the 'Key Column' previously. Even though the DSV has the relationship between Child ID and Parent ID clearly defined, the dimension wizard attempts to build a parent-child hierarchy using the Surrogate Key and Parent ID.

I have tried building this dimension using the Child ID as the 'Key Column' instead and the structure seemed to turn out okay. However, as there is no relationship between Child ID and the Fact Table this arrangement meant that the cube process resulted in no data being displayed.

If anyone can please shed light on this frustrating issue I will be very grateful.

Thanks,

Stu

Some earlier posts in this forum have discussed similar parent-child scenarios. One solution which should work, but may increase cube processing time, is to substitute a Named Query for the fact table. This Named Query would join the fact and dimension tables on the Surrogate Key, so that the Child ID gets added as a field to the resultant fact table. Then the dimension can be built from another Named Query on the dimension table, which eliminates the Surrogate Key. The Child ID could now be the key column, which you said worked OK.|||

Thanks Deepak.

I'm aware that there are several work arounds but very surprised that this cannot just be resolved within Analysis Services (excluding the use of named queries in the DSV).

Regards,

Stuart

AS2005... Using Attribute Relationships

I assume defining attribute relationships can be advantageous for performance. However, I don't quite understand them.

I know by default, the dimension is created with all attributes having an attribute relationship under the key -- with all having a cardnality of Many.

My Customer dimension points to the following named query:

SELECT customer_key, customer_id, customer_name, city, post_code, region_code, country_code
FROM Customer C
LEFT OUTER JOIN [Customer Address] CA
ON C.customer_key = CA.customer_Key
AND CA.[Address Type] = 'MAIN'

Right now, here's what I have done for the attribute relations in additon the ones created by default:

I dragged Country Code under Region code, Region Code under Post Code, and Post Code under City. The cardinality for all of these relationships is Many (not sure I understand this property). Does that sound correct?

So what do I do for Customer Id and Customer Name? I assume there should be a relationship between the two, but which field do I drag to which? Customer Name under Customer Id or vice-versa? How does it make a difference in this case, if it does at all? Cardinality?

And finally, do I want to delete the relationships that were created by default under customer_key? By the way, the customer_key attribute is not visible.

Sorry for so many questions, but I definitely appreciate your help!

There is an extensive discussion of attribute relationships and their importance in the Project REAL Analysis Services Technical Drilldown white paper.

It is an EXTREMELY important topic (in my estimation probably *the* most important to understanding the basics for how AS2K5 is different from AS2K

AS2K5 is an attribute-based system; AS2K is a hierarchy-based system.

And the core of that is attribute relationships.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave

AS2005. Strange MDX behaviour.

Hi, MDX gurus,

I have a unexplainable problem with pretty easy MDX.

Following MDX queries

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

provide the same result as expected.

This query

//
select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

returns as expected one member

but this query

select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

retuns nothing. This is strange, isn't it?
Can anybody explain it?

Thanks in advance,

Vladimir Chtepa

The puzzle for me is not why your fourth query doesn't return anything - I don't think it should - but why the third query does return July 2004. In your third query you're filtering the members on [Date].[Calendar].[Month] and for each one checking the currentmember on [Date].[Date] - but the currentmember should be the All Member on [Date].[Date] in all cases, as the following queries show:

with member measures.test as [Date].[Date].CurrentMember.membervalue

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

and

with member measures.test as [Date].[Date].CurrentMember.membervalue = CDate("11/07/2004")

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Very strange...

Chris

|||

I don't know why 3-d query returns "expected" result.

It will be great If anybody from developer team could explain it.

AS2005. strange Error in MDX.

Hi AS Fathers,

If AS runs under account with no english locale (for example 'de' or 'ru') , the following query

select {[Product].[Dealer Price].&[1.374]} on 0 from [Adventure Works]

returns nothing, but this one

select {[Product].[Dealer Price].&[1,374]} on 0 from [Adventure Works]

returns a member with unique name [Product].[Dealer Price].&[1.374]

Under such circumstances it isn't possible to select element from member list in oder to set filter etc.

there is some thing strange in forum. 1 hour ago here was a message from Mosha. Where is it?

AS2005. Some questions about AMO behaviour.

If the DimensionAttribute.EstimatedCount is set and Dimension.Update is called, should I call Database.Update?
Should I call Dimension.Update for every dimension or I can only call Database.Update?

If the Partition.EstimatedRowCount is set and Partition.Update is called, schould I call MeasureGroup.Update?

What will be used at aggregation desing Partition.EstimatedRowCount or MeasureGroup.EstimatedRowCount? What Field schould be set?

The Update() method saves the object's properties and collections, except the major children. For example, cube.Update() will save the cube, except its measure groups, perspectives etc; dimension.Update() will save the dimension, except the dimension permissions (its only major children).

The Update methods also has flags to save an object fully (with all its major children):

obj.Update(UpdateOptions.ExpandFull).

In addition, if you are doing structural changes to an object (changes that would invalidate the data and would require re-processing), you will need to save the dependents as well. For example, if you remove an attribute from a dimension, you will need to save all the dependent cubes with that dimension. There is a separate flag for this:

dimension.Update(UpdateOptions.AlterDependents); // this saves the dependent cubes also

(changes to the EstimatedCount and EstimatedRows properties are not structural, you will not need this flag)

> If the DimensionAttribute.EstimatedCount is set and Dimension.Update is called, should I call Database.Update?

No need to call database.Update(). In fact, database.Update() won't save anything from the dimensions/cubes/mining structures.

> Should I call Dimension.Update for every dimension or I can only call Database.Update?

Yes, for each change to a dimension, call dimension.Update(). Alternatively, you can do all the changes to the dimensions without saving them, and call Update(UpdateOptions.ExpandFull) to the database at the end. But this is not a good idea because it saves the full database (including all dimensions, cubes, mining structures, permissions, partitions, everything; not optimal).

> If the Partition.EstimatedRowCount is set and Partition.Update is called, schould I call MeasureGroup.Update?

No need to call measureGroup.Update().

> What will be used at aggregation desing Partition.EstimatedRowCount or MeasureGroup.EstimatedRowCount? What Field schould be set?

Aggregations design requires the following properties to be set:

- EstimatedCount for all attributes for all dimensions used in the MeasureGroup where the AggregationDesign object will be created

- EstimatedRows for the MeasureGroup

Adrian Dumitrascu

|||

Thank you very much for the detailed answer.

If I understand you right, then I don’t need set Partition.EstimatedCount at all.

Is this right?

AggregationDesigns belongs to a MeasureGroup.

And in a Partition I can set one of the MeasureGroup AggregationDesigns.

What does it means?

|||

> If I understand you right, then I don’t need set Partition.EstimatedCount at all. Is this right?

Yes, you don't need to set the Partition.EstimatedCount in order to design aggregations.

> AggregationDesigns belongs to a MeasureGroup. And in a Partition I can set one of the MeasureGroup AggregationDesigns. What does it means?

You can have multiple Partitions share the same AggregationDesign. Unlike in AS2000 where aggregations were per partition and usually those aggregations had the same structure (same attributes used).

Adrian Dumitrascu

AS2005. Distinct count meausure + aggregate seems to be a bug.

Look at following Query

-- the first query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count],[Measures].[Internet Sales Amount]})

ON ROWS

FROM [Adventure Works]

--and the second query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count]})

ON ROWS

FROM [Adventure Works]

The first query gets wrong result for the distinct count measure, the second one gets the right result.

I have tested it against build 2184 and buld 2195. Both have the same result.

Is the bug already fixed in SP2?

Yes, the problem was fixed in SP2. A public CTP release is scheduled to come out in a few weeks.|||

It would be more exectly if you wrtie "is scheduled to come out in a few weeks - 1", because you wrote more then a week ago the same words "a public beta release is scheduled to come out in a few weeks."

|||Nothing has changed to the release schedule since last time but unexpected things could happen before the software is actually released. Just don't want to promise you a specific date since it is out of my control, or any individual's control for that matter.

AS2005. Distinct count meausure + aggregate seems to be a bug.

Look at following Query

-- the first query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count],[Measures].[Internet Sales Amount]})

ON ROWS

FROM [Adventure Works]

--and the second query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count]})

ON ROWS

FROM [Adventure Works]

The first query gets wrong result for the distinct count measure, the second one gets the right result.

I have tested it against build 2184 and buld 2195. Both have the same result.

Is the bug already fixed in SP2?

Yes, the problem was fixed in SP2. A public CTP release is scheduled to come out in a few weeks.|||

It would be more exectly if you wrtie "is scheduled to come out in a few weeks - 1", because you wrote more then a week ago the same words "a public beta release is scheduled to come out in a few weeks."

|||Nothing has changed to the release schedule since last time but unexpected things could happen before the software is actually released. Just don't want to promise you a specific date since it is out of my control, or any individual's control for that matter.

AS2005. Aggregate - Last Argument = Bug?

Hi,

some people say that the function aggregate() perfoms quicker without last argument. It's right.
But witout last argument it can returns wrong result. It is nasty :-(

For example

WITH
member [Date].[Calendar].[Column 0] as AGGREGATE({
[Date].[Calendar].[Month].&[2004]&[1],
[Date].[Calendar].[Month].&[2004]&[2],
[Date].[Calendar].[Month].&[2004]&[3],
[Date].[Calendar].[Month].&[2004]&[4],
[Date].[Calendar].[Month].&[2004]&[5],
[Date].[Calendar].[Month].&[2004]&,
[Date].[Calendar].[Month].&[2004]&[7],
[Date].[Calendar].[Month].&[2004]&}
)
--, Measures.CurrentMember)

SET [RowSet0] AS {[Customer].[Customer].[All Customers].children}
SET [RowSet1] AS topcount([RowSet0],100,([Date].[Calendar].[Column 0],[Measures].[Internet Sales Amount]))
SET [TotalSet] AS [RowSet1]

SELECT {[Date].[Calendar].[Column 0]} ON COLUMNS,
CROSSJOIN({[Customer].[Customer].[All Customers],[RowSet1]},{[Measures].[Internet Sales Amount], [Measures].[Internet Order Count]})
ON ROWS
FROM [Adventure Works]

I wonder... is this the same problem as I describe here:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!889.entry
? Looks very much like it. In which case it's a feature and not a bug, but I agree, it's a pretty confusing one and something that does need to be changed.

Chris

AS2005 without Sql Server 2005

Is it possible to install and utilize AS2005 without SS2005 anywhere in the picture?

We'd like to use our current Sql Server 2000 setup with AS2005 (preferable on the same machine).

Any insights or links are appreciated.

Thanks,
JGPAS2005 can hook back into SqlServer 2000. Not sure that I'd run Beta on production just yet, when the release comes out it would probably be doable. The over head of both SqlServer 2000 and Analysis server 2005 should be considered, but technically it should be doable.

Mark E. Johnson

AS2005 Slow initial cube opening

Hi

We are having a problem in our AS2005 cubes that the initial openeing of the cubes is exceptionally slow. When the user connects though ProClarity, or I open the cube in the designer, the first report or first display of data takes about 2 minutes to return. Once it is open, the next query that you run is fine. If you exit the application, and go back in, you have to wait again for the first report.

This is very annoying to the users and frustrating for me as I cannot see anything that could be causing this.
We are running Windows Server 2003 x64 edition SP1 with SQL AS2005 x64 SP1.

Has anyone seen this before or can point me in the right direction where to look to solve this issue.

Thanks
Michael

Are you running ProClarity Professional directly againts the cube or are you using http access though ProClarity Analytics Server?

Caching of data will take some time, after a full process or restart of the server(the first user will have to pay the price of waiting) but two minutes seems to be to much. My customer run on a less powerful server than yours and the client access is though Citrix. In the morning our responstime is 30 sec to one minute.

Can you run profiler on SSAS2005 and see what happens during these two minutes?

Regards

Thomas Ivarsson

|||

we are accessing the cube directly, not using http.

The issue is that The does not only occure on the first query of the morning after a process, but on every new login during the day. I did a trace on the AS DB and there were a lot of "Discover Begin" and "End" EventClass events occuring before the MDX query was run. The were doing a lot of "10 - MDSCHEMA_MEMBERS" EventSubclass.

Could this be caused by the way the Dimensions are setup?

Thanks

Michael

|||

It can be something regarding dimension design. Do you have attribut relations defined in user hierarchies and are you using rigid relationship types? Check this Blog for more information: http://blogs.conchango.com/christianwade/archive/2006/07/12/AS-2005-Optimization-Experiences.aspx

Regards

Thomas Ivarsson

|||

Thanks, we are busy looking at a redesign of this Cube. This was designed on the Sept CTP last year, before there was any blogs about optimisations or anything like that, hence the bad Dimension design. I really just wanted to ensure that there wasn't something else out there that could be causing this.

Regards
Michael

AS2005 September CTP Memory Error

I get "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed." while executing query set with cube browser in SQL Server BI Dev Studio. The same error I get while executing query with VB code (via ADOMD). The source of the error is AS2005 OLEDB Provider.

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||

From another message board:

This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks

|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...
|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.

|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||

Luther Smith wrote:

I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

AS2005 September CTP Memory Error

I get "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed." while executing query set with cube browser in SQL Server BI Dev Studio. The same error I get while executing query with VB code (via ADOMD). The source of the error is AS2005 OLEDB Provider.

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||

From another message board:

This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)|||

Luther Smith wrote:

I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

AS2005 September CTP Memory Error

I get "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed." while executing query set with cube browser in SQL Server BI Dev Studio. The same error I get while executing query with VB code (via ADOMD). The source of the error is AS2005 OLEDB Provider.

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||

From another message board:

This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks

|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...
|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.

|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||

Luther Smith wrote:

I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

AS2005 September CTP Errors when Processing Cube

I have a cube which was originally created in the June 2005 CTP.

I have made no changes. I am just attempting to process it in the AS2005 September CTP.

I get random messages like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_PolicyFact, Column: PolicyDimKey, Value: 3135. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Policy Number of Dimension: PolicyA from Database: CDEV15D1, Cube: Cube, Measure Group: Policy Fact, Partition: Policy Fact, Record: 2920.

While trying to process various fact tables. Any ideas? I checked and the key relationships in the source store are all good.

Thanks,

ChrisI had the same problem. The cube processed fine on June CTP but would not process on the September CTP. I discovered my problem was due to the way dimension key errors were handled.

For the "key not found" option on the dimension key errors tab of the processing box, change the default setting from "Report and continue" to "Ignore".|||

Hi guys,

Thanks a ton for that input. Can you believe it! I am working on the Adventure works sample db and get the same error when processing a simple cube metioned in the tutorial.

I think this option was set to "Ignore" in the CTPs and then it gor switched back in the final release.

Thanks once again! Keep up the good work!!

AS2005 September CTP Errors when Processing Cube

I have a cube which was originally created in the June 2005 CTP.

I have made no changes. I am just attempting to process it in the AS2005 September CTP.

I get random messages like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_PolicyFact, Column: PolicyDimKey, Value: 3135. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Policy Number of Dimension: PolicyA from Database: CDEV15D1, Cube: Cube, Measure Group: Policy Fact, Partition: Policy Fact, Record: 2920.

While trying to process various fact tables. Any ideas? I checked and the key relationships in the source store are all good.

Thanks,

ChrisI had the same problem. The cube processed fine on June CTP but would not process on the September CTP. I discovered my problem was due to the way dimension key errors were handled.

For the "key not found" option on the dimension key errors tab of the processing box, change the default setting from "Report and continue" to "Ignore".|||

Hi guys,

Thanks a ton for that input. Can you believe it! I am working on the Adventure works sample db and get the same error when processing a simple cube metioned in the tutorial.

I think this option was set to "Ignore" in the CTPs and then it gor switched back in the final release.

Thanks once again! Keep up the good work!!