Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

dimension design

I have a fact table and dimension. a view is taken as base table for Dimension which will Productkey,LevelId. One product may exist in multiple levels. so we have multiple records for a single product.

And in fact table we have productkey as foriegnKey. When i relate fact n dim to create a cube, cube considering one record for each product key in fact table. its automatically avoiding other records. can we any way stop this happening? OR is this default behavior?

Thanks in adv

Do you have a self-join in your product dimensions table between productid and parentproductid? This sounds to me as if you have a parent-child dimension/structure. In the dimension wizard you will see, in one of the final steps, a question/page where you can tell if you have a parent child structure.

You will also check if you are not having a many-to-many relation between the fact table and the product dimension table.

The third point is that you can add the level to the productid and make a unique technical key for each kombination of product and level. This will have to be matched with the records in the fact table.

HTH

Thomas Ivarsson

|||

Thanks for the response.

3rd point is not possible to implement..let me check with first point.

so does it ignore multiple records for same productKey?

|||

Hello again. In the AdventureWorks sample database you have table, Production.BillOfMaterials that you can have a look at. In the Adventure Works DW sample database you have the account dimension with a more simple parent child relation.

I am not sure what you mean by "ignore multiple records for the same productkey.

With parent-child dimensions you can have data/facts in the fact table that is not only from the leaf level but also from higher levels.

HTH

Thomas Ivarsson

|||

In this case its not possible to implement Parent-Child dimension. and there is also situation that one child product can have multiple products as parents. Its some thing like many-many in Parent-child.

|||

Looks like you have to use many-to-many dimensions then.

Here is a link to some help: (http://www.sqlbi.eu/Default.aspx?tabid=80)

HTH

Thomas Ivarsson

|||

Before coming to forum for the solution, i had tried m2m dimension. When i took the view as dimension where one product can exist in multiple lelvels. table will look some thing like this

leafPKey 5233 5233

L1PKey 9 10

L2PKey 31 15

L3PKey 632 632

L4PKey 2572 2572

L5PKey 5233 5233

When this table is related to fact table only one 5233 is considered. 5233 is only visible under l1pkey= 10.

is this default behavior, or can i get both the ids by changing some thing?

when a single record is only considered m2m relation is existing.

Thanks in adv

sql

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;

1. add an additional columnn to your fact table for price range, make it an integer

2. create a dimension for these price ranges

3. apply the case statement you have here as an update to that

fact table and change the terms ('Expensive', 'Moderate', etc.) to

integers that match the counterparts in your new price range dimension

4. add the new dimension to the cube schema, etc.

5. reprocess

Unless I am not understanding what you are going for here that should do it.

Edward R Hunter|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;
1. add an additional columnn to your fact table for price range, make it an integer
2. create a dimension for these price ranges
3. apply the case statement you have here as an update to that fact table and change the terms ('Expensive', 'Moderate', etc.) to integers that match the counterparts in your new price range dimension
4. add the new dimension to the cube schema, etc.
5. reprocess
Unless I am not understanding what you are going for here that should do it.
Edward R Hunter
|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Dimension [] and Measure [] have no relation in Excel Addin

Hi,

I am kind of new to SSAS 2005 but done some work on sql 2000 MSOLAP. There is a difference I spotted between the product which is annoying me and not sure if there is a solution for the same.

In SQL 2000 MSOLAP i created one cube which had only is related to active outlets listing of company only related to one dimension.

I added this to virtual cube which had many other cubes and many other dimensions. Based on virtual cubes I created reports and caculated measures using the active outlet listing without having to worry about it being linked to other dimensions. It always used to generate data in excel addin where many dimensions where put in page and row sections.

Now in SSAS I created it as part of standard cube related only to one dimension but if use it excel addin with other dimensions it gives me an error saying the Dimension [Year] and Measure [Active outlet listing] has no relationship.

How do I handle the above. Any quick help will be appreciated.

Thanks & Regards

Ramnath

This issue was discussed some time back in Chris Webb's blog:

"I did spot a problem that I've seen in other AS2005-enabled clients and which I hope won't turn into a trend. I found it by creating a report using Adventure Works, putting the measure [Internet Sales Amount] on columns and trying to put something from the Geography dimension on pages, which resulted in a dialog box informing me that the Geography dimension was unrelated to the [Internet Sales] measure group and stopping me from completing the operation. Now in 99% of cubes this would be a good thing to do, but I've already built a few cubes where I have dimensions that have no relation to any measure group but where selections on them do impact calculations (think solving the start/end date problem, where you might want to create an end date dimension with no relation to a measure group); of course, this client feature stops you from being able to design cubes in this way."

And there was this comment suggesting a work-around, if it helps you:

Mosha

"In the meantime, the workaround would be to create calculated measure which redirects to the physical measure ?"

December 29 12:10 AM
(http://www.mosha.com/msolap)

|||

Thanks Deepak,

I actually tried the work around before reporting this as a problem. The work around becomes very cumbersome when we have large number for hierarchies for one dimension.

I found the easier way by creating associations for all measures groups to all dimensions. The association is at the highlest level in all dimension structures.

How can we report this issue to Microsoft and get help in fixing it in the Excel addin. Does any body know if there is going to be maintenance release for this Excel addin.

The Excel addin link page also does not work so people are only able to see the FAQ section and download section of this addin. Other details of this addin are not available.

Thanks For all the support.

Differences between 2005, 2000, and Express - documentation, whitepapers?

Can anyone point me in the direction of some NON-sales documentation on the differences between these product? I am sure, especially with Express, there are considerable functionality and architecture differences.

I've looked in BOL, and I've done searches online.

All i seem to get is sales related stuff.

I'm curious about the architecture of SQL 2005, SQL express. In 2000, there was some fairly detailed documentaiton on this subject, but 2005 BOL seems REALLY diffucult to find things.

I may just need to try different keywords...

Here is a good page for 2005: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||

Hello all, two questions that perhaps are related:

Is it a bad idea to use sqlexpress for systemtests if prod-environment is sql2005? I am using stored procs against tables, simple techniques. No fancy stuff like external functions, webservices or similar .

I have to use standard security with a sqluser but I get authorisation failure. Do I need to set sqlexpress to allow mixed mode? How do I do that?

Thanks in advance!

/Rasmus

|||

yacrumb2,

Yes, you can use SQL Express for testing; you wouldn't be able to test SQL Agent Jobs and some of the other more 'advanced' functionality. But for simple stored procedure testing, there should be no problem. However, I would recommend that your test environment closely 'match' the production environment. (Or that you use the Developer's Edition -which cost only about $50 US, and is the same as Enterprise Edition)

Any version can use SQL Authentication, just set for 'Mixed Mode' on the security tab of the Server Properties window in SSMS (or SSMSE). If you are having problems, you may wish to post the error message and how you are trying to connect so we can help you.