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 MDX-Question (Grouping while ignoring intermediate level)

Hi,

I'm quite new to MDX and try to solve the following problem: given is a dimension having the following hierarchy:

Business Unit A
Sales Area 1Customer XCustomer YCustomer ZSales Area 2Customer VCustomer WCustomer X
Business Unit BSales Area 3......

Now I need the sum of each business units' revenues grouped by customers. In the above example the result should look like:
Business Unit A

Customer VCustomer WCustomer X (sum of Sales Area 1 and Sales Area 2)
Customer YCustomer ZSales Area 2Business Unit BCustomer ...
...
Is this possible with MDX?

Thanx for any help and kind regards,
Gerald

Gerald, try this:

{GENERATE({[OrgDimName].[HierarchyName].[BusinessUnitLevelName].MEMBERS},

{[OrgDimName].[HierarchyName].CURRENTMEMBER,

DESCENDANTS([OrgDimName].[HierarchyName].CURRENTMEMBER,

[OrgDimName].[HierarchyName].[CustomerLevelName], SELF)})} ON ROWS

If you replace SELF with SELF_AND_BEFORE you will also get the Sales Area sub-totals.

HTH

Philip Taylor

|||Philip, thank you for your answer.
What I forgot to mention (and is quite important, I think) is that the dimension is a parent-child-dimension:
business units are on [Level 04]|||

Gerald,

Can you explain the meaning of revenue(X1) and revenue(X2) in relation to Customer X please.

Also you might want to create a level naming template ( see http://msdn2.microsoft.com/en-us/library/ms167115(d=ide).aspx and there is an equivalent in AS2000) which I think makes it easier to navigate your org structure and write MDX queries.

Regards

Philip

|||Philip,

the formula in brackets is just thought as an explanation of how the revenue should be calculated. So the revenue for business unit 1 should be the sum of the revenues of customers V, W, X, Y and Z where customer X appears twice, once with revenue value X1 and once with revenue value X2. In other words "revenue(n)" means the revenue value of customer n stored in the facts. I just see that I've made a typo at customer W: of course it should be revenue(W).

Anyway - I have just had a talk with the guy responsible for the cube design and it looks like we will make a second cube which is optimized for reporting. In this case I'll have a dimension which gives me the desired structure directly and I won't have any problems getting the results our customer would like to see.

thanx and kind regards,
Gerald

AS2000. How to organize pocessing?

In AS 2000 I made update processing off all dimension and full processing of the last partition in every cube. I was made in a transaction. Duering processing the old version of data was available for the MDX querying.

If I do the same in AS 2000, the database seem to be locked.

What I make wrong?

Hiere is the xmla batch what I send to the AS2005.

<Batch Transaction="true" ProcessAffectedObjects="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Abteilung</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Adm</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Artikel</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>Verkauf</MeasureGroupID>

<PartitionID>Verkauf_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufAuftrag</MeasureGroupID>

<PartitionID>VerkaufAuftrag_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufRechnung</MeasureGroupID>

<PartitionID>VerkaufRechnung_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufArtikel</MeasureGroupID>

<PartitionID>VerkaufArtikel_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

</Parallel>

</Batch>

Do you mean "If I do the same in AS 2005, the database seem to be locked" ?

If this is related to Analysis Services 2005 behvior, this is strange. I think you should have access to product support. Please report this problem.

Thanks.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

AS2000 - Different default dimension member according to cube

Dear all,

Is it possible, in AS2000, to choose a different default dimension member according to the cube we are looking at? I'm in a project where the Accounting cube should open on the last closed month and the sales cube on the current month. Is this achievable?

Regards,

Pedro Martins

Portugal

Hello. This is from my(weak) memory. If this is possible in SSAS2000 you should be able to set the default member on the dimension in the cube editor.

Have you checked this?

I do not think it is possible if you use a shared time dimension in two cubes. I think that you set the default member in the dimension editor(advanced properties).

One solution is to build two separate time dimensions, shared or cube.

Another approach depends on if you have a third party tool for building reports on top of SSAS2000, like ProClarity. If this is the case you can build two different named sets in each cube(on the same shared dimension) that points to the different default members, and use them in the tool.

Regards

Thomas Ivarsson

AS 2005 Slowly Changing Dimension - adding new rows when no change

Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.

The other problem is the package is not tracking changes it is spending a lot of time doing lookups (it's slow), but ends up creating new records when there has not been a change.

I'm quite sure the business key is set up correctly (I'm using the PK from the source table).

The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).

Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?sorted out his issue

http://forums.microsoft.com/MSDN/showpost.aspx?postid=1475782&siteid=1