Another issue with reference dimensions

Hi guys,

I encountered another issue with reference dimensions which I'm not sure if it's not by design. So I decided to ask

The problem is that I have the following:
- Fact table
- Dimension1
- Dimension2

Dimension1 is having a regular relationship to the fact table. When I deploy the project at this point, everything is working as expected - I can slice the measures by all the memebers of Dimension1's attribute hierarchies.

When I set Dimension2 as a reference materialized dimension to the fact table, using Dimension1 as a reference dimension, I encounter a big problem. As far as I understand how reference dimensions work, what happens behind the scenes is that the a FK to the referenced dimension (Dimension2) is also added to the fact table so that the slicing is possible. However, considering that I don't have a record in dimension2 for each record in dimension1, the result is that in the fact table I don't get all the possible slicings for dimension1 which I used to have before that. I guess that the SQL query it builds for populating the fact table is now an inner join with both tables (dimension1 and dimension2) which is perfectly valid if I'm slicing only through dimension2, but I still need the results which I got before that for dimension1 and the fact table.

I don't know if I made it clear, but the bottom line is that I need to be able to slice normally by all records in both dimensions - the usual and the referenced ones. Currently the setup (at least by default) stores records in the fact table only for the intersection of the two, which is undesired behavior in my case.

Any ideas if this is by design or if there's something wrong that I'm doing?

Cheers,
Alex
I forgot to mention that if I set the reference relationship not to be materialized, then everything works perfectly. But as from what I've read this is really not a good idea cause it's much slower.

So the question is - could I achieve the same behavior by preserving the materialization of the referenced relationship?
|||Any ideas?

Another aggregation design question

Guys,

I model a cube with dimensions, I create the partitions and their aggregations, then I change the dimensions structure or I change the dataview.

Cube does not complain about aggregations at any time during the change process.

So what is this all about?

I suspect I should run a re-design of aggregations when I am done with structural changes but I wonder why I am never warned about existing aggregations becoming obsolete?

So when should you re-design aggregations? (I use 30% then I add usage based after a while).

Thanks,

Philippe

Yes, you need to go an re-desing your aggregations after you've made stuctural changes to your dimension: You've added, or removed attrubutes from the dimension, you've changed attribute relationships you should go and re-desing aggregations.

If you defined natural hierarchies ( you can search this forum for the definition of these) 30% should be a good start. But in case and if you are using just regual hierarchies without defining attribute relationships, or you browse attributes using attribute hierarchies, you should go over all the attributes you browse by and change AggregationUsage property to Full or Unrestricted.

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

Anaylsis Server 2005 and wrong results in dimensions/cube

My cubes are somehow incorrect and I can't find out why:

I created a very simple table, because of wrong results in my project cubes and inserted 102 rows:

-

USE EA_DWH
GO

CREATE TABLE [dbo].[TEST_DWH]
(
[DWSTOREDATE_INT] INT NOT NULL,
[USER_NAME] VARCHAR(20) NULL
)
GO

INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021217','USER_03')
INSERT INTO TEST_DWH VALUES('20021218','USER_04')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
GO

-

The first value of this table represents the date value as integer and the second value is the user name.

Now when building a cube (I) you will find out that the values differ to a simple group by on the sql database (II):

I count all user and the dimension is build on DWSTOREDATE_INT (logical key) as key attribute and USER_Name as regular attribute.

(I):

-

TEST DWH Count TEST DWH USER NAME Ergebnis 20021216 USER_99 22 20021216 Ergebnis 22 20021217 USER_03 1 20021217 Ergebnis 1 20021218 USER_04 1 20021218 Ergebnis 1 20021219 USER_01 62 20021219 Ergebnis 62 20021220 USER_01 16 20021220 Ergebnis 16 Gesamtergebnis 102

-

(II):

-

SQL query (SQL Server):

SELECT DWSTOREDATE_INT, USER_NAME, COUNT(*) AS Count_StoreDate_User
FROM TEST_DWH
GROUP BY DWSTOREDATE_INT, USER_NAME
ORDER BY DWSTOREDATE_INT

RESULT:

DWSTOREDATE_INT USER_NAME Count_StoreDate_User
-- --
20021216 USER_99 22
20021217 USER_03 1
20021218 USER_04 1
20021219 USER_01 62
20021220 USER_01 8
20021220 USER_02 5
20021220 USER_03 3

(7 row(s) affected)

-

I tried this example on three different computers and can't find out why this happens. On Analysis Server 2000 it works correctly.

My computer:

W2K3 (SP1), SQL Server 2005 EE (SP1 CTP)

I had the same results without SP1 CTP and installed it because I thought this issue may be corrected.

Please help me solve this problem.

Best regards

You are correct. This is one of the cases where AS2005 is different from AS2000.
In your example you have a case of dimension that is based on the non-unique key attribute.
In AS 2000 Analysis Server automatically makes a dimension key unique by concatenating all levels to the key level ( lowest level in the dimension). In AS 2005 this is no longer the case. Although in simple cases (like you have here) you might be little confused at first, the real gain comes in AS2005 being able to implement better performing and scalable dimensions.
To fix the problem in your dimension, you need to define your composite key for your dimension key attribute. Add USER_NAME column to the KeyColumn of your dimension key attribute.

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

Analysis Services: Deploy a cube

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. ThanksJodJim,

I am assuming you properly specified target server name. Next steps:
1. Make sure that SQL Server Analysis Services service is running. It is not the same service as SQL service. You can do this in Adminitrator Tools ->Services. This is most likely cause why you cannot deploy
2. Make sure you are member of administrators local group on SSAS server. This will give you admin right required to deploy database.

If that does not work:
Start SQL Server Management Studio and connect to analysis services. Any problems? Any messages?

Vidas Matelis
http://www.ssas-info.com
http://www.ssas-info.com/VidasMatelisBlog

Analysis Services Developer Studio Question

I had an obsolete dimension on my cube.

I delete the dimension, it disappears from the GUI (I can see all the other dimensions in the dimensions panel, but this one correctly disappears), yet when I try to build my project I get errors on this dimension. It's like the dimension was deleted from the GUI but still exists somewhere and is causing problems.

How do I resolve this without rebuilding my cube entirely? How do I delete this dimension completely?Are you sure that dimension is absolete ?
If it is not - error looks correct ...
What error do you receive ?

Analysis Services Cube Measures showing #ERR

Hi,

We have a MS OLAP Cube using 10 dimensions and 1 fact table with measures as COUNT and DISTINCT COUNT.

When we processed the cube using Analysis Services, it was done but all the measures are showing #ERR as values. The cube is about 650MB in size and all the joins between Dimension and Fact tables seem to be OK.

What could be the possible reasons of getting #ERR in measures? Do all the datatypes and length of JOIN columns between dimension and fact HAVE TO BE the same??

Please provide any inputs as this is very urgent.
Thanks
Omkarid assume its some division by 0 error

you can deal with this by replacing a Zero with a NULL in whatever field you use as a denominator. (the bottom half of division-- i hope that was the right word)

Analysis Services 2005 Cubes

Hi,

I had designed the DW database like

1. For each report one fact and some dimensions are created

2. Now i want to create a cube. while creating a cube i got some doubts. i want to know that what is the better way to create a cube

2.1 Whether i have to use one cube for one report

or

2.2 Whether i have to create only one cube and i have to create different measures

Doubts:

1. If i go to option 2.1 then it takes more memory? (because each cube stores the data in structres)

2. if i go to option 2.2 then it hits performance? (because several users access several reports using the same cube)

Can you please help me in this.

Thanks

Dinesh

If you have common structures between your reports you will probably get much better performance and memory useage from haveing multiple measures in a single cube rather than a cube per report. The indexes and caching could all be shared between the measures increasing the performance.|||Thanks.

Analysis Services 2005 Aggregations?

Hi,

When i try to calculate the cube aggregations it will always give me 0 aggregations... why is this happening? I have about 10 dimensions and if i remove 3 of them (always the same dims) the aggregations are calculated to about 99% so why the hell with that dims it would not calculate? They are Parent-Child Dimensions...

Best Regards,

Luis Sim?es

Looks like a bug to me.

Can you please try and contact Customer Support and open a case about this behavior. It would be helpful if you will be willing to share your cube design.

Thanks.

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

|||

How do i contact Customer Support?

About the cube i really can't post anything about it because it is beeing done to a major public entity and i have no authorization to pass schemas, designs or data out of here...

So is there anyway we can synch and try to find the problem?

Best Regards,

Luis Sim?es

|||

http://support.microsoft.com/ph/2855

See if you pass the information to Customer Support in more general terms. How many dimensions, how many attributes per dimension. How many hierarhies in every dimension.
To try and reproduce the problem in generic version of your cube.

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

|||

I am doomed! The cube is so slow that i can't do anything with it... Jesus....

Has for the microsoft site i was unable to find a direct way of contact with them unless i have to pay for support...

Do you have any sugestions?

U have msn?

Regards

|||

Luis.

It is really hard or nearly impossible to help you with very short description of the problem.
It is even harder to help if you cannot share any of your data.

Can you try and describe your cube design in general terms like:

Dim1 ( KeyAttr, Attr1 , Attr2 ....) Parent-Child
Attr1 is related to KeyAttr and usage is set to Parent.

Dim2 (KeyAttr, Attr1 , Attr2 ) Regular
Hierarchies Hier1 (Attr2, Attr1, KeyAttr)

....

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

|||

Well i am shure now that the problem lays within the Parent-Child Dimensions because i have created new dimensions for the same purpose but flattened.. no parent-child and the cube gets 95% aggregated...

So basically all my Parent-Child Dimensions are done like the book says...

So i have A Parent Atribute and a Key Atribute

Example: Products Dimension

PARENT_PRODUCT_COD -> this is the parent atribute

PRODUCT_COD -> this is the key atribute

In the parent atribute i also have parameterized the UNARY OPERATOR and ROLLUP FORMULA with the correct columns

So i don't really know where is the problem...

Regards,

|||

The solution for your problem ( I got better description of it through the MS guys in Portugal) is:

For every PC dimension you should change AggregationUsage property of the Parent attribute to None.
For every Key attribute you should set AggregationUsage to Unrestricted.

Once you've done this, you should see aggregation desing algorithm desinging aggregations for you.

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

|||

Edward,

I have received from Jose Silva (MS Portugal) the changed project, after calculating the aggregations i still get 0%....

It seems it is a known bug by AS 2005 programmers team and probably only solved by a hotfix...

Lets see what happens in the next days... and then i will post the result here so the community can be informed about this problem.

Thank you!

Best Regards,

Luis Simoes
WorldVision Consulting

Analysis Services 2005 Aggregations?

Hi,

When i try to calculate the cube aggregations it will always give me 0 aggregations... why is this happening? I have about 10 dimensions and if i remove 3 of them (always the same dims) the aggregations are calculated to about 99% so why the hell with that dims it would not calculate? They are Parent-Child Dimensions...

Best Regards,

Luis Sim?es

Looks like a bug to me.

Can you please try and contact Customer Support and open a case about this behavior. It would be helpful if you will be willing to share your cube design.

Thanks.

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

|||

How do i contact Customer Support?

About the cube i really can't post anything about it because it is beeing done to a major public entity and i have no authorization to pass schemas, designs or data out of here...

So is there anyway we can synch and try to find the problem?

Best Regards,

Luis Sim?es

|||

http://support.microsoft.com/ph/2855

See if you pass the information to Customer Support in more general terms. How many dimensions, how many attributes per dimension. How many hierarhies in every dimension.
To try and reproduce the problem in generic version of your cube.

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

|||

I am doomed! The cube is so slow that i can't do anything with it... Jesus....

Has for the microsoft site i was unable to find a direct way of contact with them unless i have to pay for support...

Do you have any sugestions?

U have msn?

Regards

|||

Luis.

It is really hard or nearly impossible to help you with very short description of the problem.
It is even harder to help if you cannot share any of your data.

Can you try and describe your cube design in general terms like:

Dim1 ( KeyAttr, Attr1 , Attr2 ....) Parent-Child
Attr1 is related to KeyAttr and usage is set to Parent.

Dim2 (KeyAttr, Attr1 , Attr2 ) Regular
Hierarchies Hier1 (Attr2, Attr1, KeyAttr)

....

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

|||

Well i am shure now that the problem lays within the Parent-Child Dimensions because i have created new dimensions for the same purpose but flattened.. no parent-child and the cube gets 95% aggregated...

So basically all my Parent-Child Dimensions are done like the book says...

So i have A Parent Atribute and a Key Atribute

Example: Products Dimension

PARENT_PRODUCT_COD -> this is the parent atribute

PRODUCT_COD -> this is the key atribute

In the parent atribute i also have parameterized the UNARY OPERATOR and ROLLUP FORMULA with the correct columns

So i don't really know where is the problem...

Regards,

|||

The solution for your problem ( I got better description of it through the MS guys in Portugal) is:

For every PC dimension you should change AggregationUsage property of the Parent attribute to None.
For every Key attribute you should set AggregationUsage to Unrestricted.

Once you've done this, you should see aggregation desing algorithm desinging aggregations for you.

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

|||

Edward,

I have received from Jose Silva (MS Portugal) the changed project, after calculating the aggregations i still get 0%....

It seems it is a known bug by AS 2005 programmers team and probably only solved by a hotfix...

Lets see what happens in the next days... and then i will post the result here so the community can be informed about this problem.

Thank you!

Best Regards,

Luis Simoes
WorldVision Consulting