Question on time dimension and time intelligence

Hi, all experts here,

I am wondering how could we actually check the time dimension and time inteligence within a cube? I mean from what property of the cube or the analysis service project that I would be able to check if there is time dimension or time intelligence implemented accordingly?

Hopy it is clear for your advices and help.

Thank you in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Helen999888 wrote:

Hi, all experts here,

I am wondering how could we actually check the time dimension and time inteligence within a cube? I mean from what property of the cube or the analysis service project that I would be able to check if there is time dimension or time intelligence implemented accordingly?

Hopy it is clear for your advices and help.

Thank you in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Helen Please help me to solve the following problem

How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00.

I wan to get 01/11/2002 dd/mm/yyyy.

What I have to do. When I changed their cell value property as dd/mm/yyyy the value is not changed still it is showing as year/Date/Month

..Please to crrect my problem

with regards

Polachah

Question on the Dimension Wizard

Hi, all,

I have a questions on the dimension wizard.

Question 1: Main table: the data source table where we want to derive the new dimension data from?

Key columns: the keys columns which are the key of the main table above?

Column containing the member name Here I am confused,), as when we choose composite keys for the main table above, we will have to choose the column containing the member name? What is this column for? And what does it really mean? This column containing what member name?

Please any of you here give me any advices on this and I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

The key columns uniquely identify each member of an attribute. This is different from the key columns of the table which uniquely identify each row of the table. As an example, if you had a column containing Month names with 12 values and another column containing years with 5 values, but you wanted an attribute that contained individual members for every month of every year, 60 values, then you would use both Month and Year as the key columns of your attribute. Now the name column determines what each attribute will be called. In this example, you could use Month as the name column, but you won't be able to tell your January of 2006 from your January of 2007 (unless you just use this attribute in a hierarchy with Year above it.) Here you'd probably want another column - likely a named calculation which contactinates month and year - as your name column.

You might want to try the Analysis Services 2005 Tutorial included with the product to help with some concepts if you have further problems with this.

|||

Hi, Matt,

Thank you very much for your kind advices.

I now have a clearer understanding of it. Thanks again.

With best regards,

Yours sincerely,

|||

Hi, Matt,

Thank you very much for your kind advices.

But I am still a bit confused as key columns are the columns which relate the new derived dimension to the fact table (where this new derived dimension table from), therefore these key columns will be the dimension granularity of the usage of the new derived dimension to the fact table. But the fact table has composite keys consisting of different FKs of different tables, in this case, the name column will be the column naming the above composite keys? Actually in fact table, there is no such columns yet, therefore I will have to go to the data source view to create a such new name column for the above key columns which make up of the composite keys of the fact table?

Hope my question is clear and please give me further advices on this issue.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

|||The key columns of the key attribute of the dimension uniquely identify each member of the attribute witht he lowest granularity in the dimension. Although it is common, it is not necessary that these key columns correspond to the relational key of the table which uniquely identifies each row in the table. It sounds like you have a table which contains both fact information and some denormalized dimensional information. In cases such as this where tables are not normalized, it is common to create dimension keys which are not the same as the table key. For example, if you had a table with sales information and it also contained the columns EmployeeNumber, EmployeeName, EmployeeDepartment, then you would probably want to create an employee dimension whose key attribute's key column was EmployeeNumber. Note that the table containing EmployeeNumber might not even have any key as it is common for fact type tables to allow duplicate transaction entries. (So a Customer would be allowed to buy the same product from the same employee on the same day, even if there is no notion of transaction ID.)|||

Hi,

Thank you for your advices. The schema of my fact table contains transactional facts, and FKs from its related dimensional tables. E.g

FK1

FK2

FK3

TransactionID

.......

Thanks again.

With best regards,

Yours sincerely,

|||

Hi, Matt,

Please ignore my previous post, I have figured out the problem.

Thanks again.

With best regards,

Yours sincerely,

Question on fact(degenerate) type of fact table relationship

Hi, all,

Could any of you give me some guidance on the fact(degenerate) type of fact tabel relationship between a dimension table and a fact table? Though its definition as: the dimension members are derived from the fact table. How does it really work?

We knwo there are degenerate dimensions which are included in fact tables e.g TransactionID etc in the underlying relational database. What is the idea of dealing with these scenario where we still want to see the degenerate dimensions in the cube dimensions instead of residing meaningless in the cube measure groups? Any best practices or suggestions?

Hope my question is clear for your help and I am again looking forward to hearing from you shortly for your advices.

With best regards,

Yours sincerely,

Hello Helen. When you build a dimension with the fact table as a data source these dimensions will not be different from dimensions with separate tables as data sources.

So you can start the dimension wizard and choose the fact table as a data source and pick the attributes that will be part of the degenerate dimension.

You can only have one degenerate dimension in a cube.

When you put them in a separate table you will have a one-to-one relation with the fact table at the leaf level so that approach is not that common.

Have a look at www.kimballgroup.com or google on degenerate dimensions.

From what I have seen they will take a little longer time to process than one-to-many dimensions.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your advices. So it works for all attributes in the dimension wizard with fact table selected as the data source? E.g. for some attibutes with only a few distinctive values (which we can put them into junk dimensions, then in this case, we dont necessary to create separate junk dimensions for these attributes which are originally residing in the fact table, instead we can use the dimension wizard to create new cube dimension with the fact table as data source)?

Therefore, in this case, we have to define the fact relationship as fact(degenerate) relationship for all cases where the dimension memembers are derived from the fact table?

Hope my question is clear.

With best regards,

Yours sincerely,

|||

Hi Helen.

You can create standard dimensions with attributes from the fact table without marking them as fact table dimension in the dimension usage tab. It also possible to mark one relation between the fact table /measure group and a dimension as a fact table dimension. Both ways will work and if you avoid the fact table relation type(dimension usage) you can make more than one.

Attributes with a few distinct values normally qualifies as standard dimensions and can be placed in a dimension table with a one-to-many relation with the fact table.

Junk or fact table dimensions normally have a one-to-one relation with the fact table.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for your very kind advices and help.

It then sounds like it is better to create standard dimensions with attributes from fact table which will enable more than one relationship types between the fact table and the derived dimension table.

Hopefully I am right.

Thank you again.

With best regards,

Yours sincerely,

Question on dimension usage of cube

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the dimension usage of cube. When we relate the dimensions to measure groups, is it a must we have to use the key of the dimension to relate to the measure groups? Otherwise the analysis services server will not be able to correctly aggregate the cube data? Thank you very much and I am looking foward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello. Yes, you use the logical dimension key that you have in the datasource view. One exception is when you have a fact table with a month dimension and your time dimension have day/date as the lowest level. In the Adventure Works cube, that is part of the installation of SQL Server 2005, you have a relation like that between the date dimension and the Sales Targets measure group. That relation is defined between the Calendar Year and Calendar Quarter columns in the dimension usage tab.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot.

With best regards,

Yours sincerely,

Question on dimension types in a cube

Hi, all experts here,

Thanks a lot for your kind attention.

I am wondering dose it really matter if the dimension types we set for each dimension will really affect anything wihin a cube?(like normally the default dimension type is regular, but there are many other dimension types like promotion, product etc. ), so do these dimension types will really affect anything for the cube data? If so, like what would be affected and changed?

I am looking forward to hearing from you shortly and thank you very much in advance for your help and advances.

With best regards,

Yours sincerely,

The only two types which affect semantics are:

1. Time - for MDX functions such as YTD and for semiadditive measures

2. Account - for Account Intelligence

Everything else are metadata that client applications can use for UI and other purposes.

|||

Hi,

Thanks a lot.

With best regards,

Yours sincerely,

|||

Dear Helen

Pleasae help me.. How can I create an attribute dateoforder in Sales dimension ...

Please help ..

Question on cube and dimension deployment in production

Hi, all experts here,

Would any of you here shed me any light on how could we deploy cubes and dimensions to other server rather than the one where they are created? And also what permissions at least are needed to be set up for them to be accessed in production enviornment?

Hope it is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

Take a look at the functionality of the Deployment Wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx

Regarding permissions, do you mean administrative access or the ability to query data? In either case BOL is again the best place to start: http://msdn2.microsoft.com/en-us/library/ms174517.aspx. Or do you mean the ability just to connect to the cube at all? In which case this new paper on connectivity problems might help - it discusses everything might might stop you from connecting:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

Regards,

Chris

|||

Hi, Chris,

Thanks a lot for your very kind help. Definitely they've been very helpful to clear up my confusions and questions on that.

Yes, I am concerning about being able to access the cubes and dimensions after they are deployed to other production environment.

Thanks a lot again.

With best regards,

Yours sincerely,