How FM works by comparing IDs regarding data type

[ratnalein]

Hallo everyone,

I have a column in my fact table:

Dim_1_ID  ....  Fact_Measure
------------------------------------
56             ....  1000
3675         ....  800
231           ....  12500
...
...
...

The dimension table for Dim_1_ID looks like this:

ID      Description
---------------------
1        ...
2        ...
3        ...
...       ...

My question:
data type in the face table --> VARCHAR(20)
data type in the dimension table --> CHAR(30)

Is this okay to leave it like that by modelling in framework Manager?  Would Query Studio and Report Studio have problem comparing the IDs because of the different data types?

Thank you for your comments.

Regards,

Ratna :)

[siyad]

It wont be having any issues as far I think.

But as a best practice if you think tht it would need a join, I would suggest to keep both the columns with the same data type and length

[Rahul Ganguli]

Hi Ratna,

Can you please explain why can't you make the ID feild as numeric. As having id in numeric is standard practice.
Since Join on a numeric feild is far more beneficial than joining on characters.

Regards,
Rahul

[ratnalein]

Hallo Rahul and Siyad,

thank you for the advice and information. 
Rahul@, I can not model the ID field as numeric, since there are IDs beginning with zero:
07289
59483
01102
and so on.

I have another question:

I have a DIMENSION table, lets say 'Country', one of its column is 'Population'.  Integer as data type.  As I said, this is a DIMENSION table.  My question:
How can I set the Regular Aggregate and Semi-Aggregate for the particular column?
Regular Aggregate --> Unsupported
Semi-Aggregate --> Unsupported

Is this correct?  Would it be wrong if I model the Regular Aggregate as Count?

Once again, it is a dimension table, so it will not be aggregated.

Thanks for the advice.

Regards,

Ratna

[blom0344]

A char datatype would imply trailing spaces, whereas varchar would not.  It would take 3 minutes to test this, so why ask?  There is a chance you will need to trim the data, which will give nasty joins, bad performance etc.

Dimension and facts should preferably have surrogate numeric keys, and with natural keys at least the same datatype

[Rahul Ganguli]

Yes, You can set the Aggregate property to none.

[blom0344]

Why would you want to perform a count on a population number?  A count is an aggregate by the way  :)

[ratnalein]

Hallo everyone,

okay, I will set the attribute "Population" in my dimension table as:
Regular Aggregate --> Unsupported
Semi-Aggregate --> Unsupported

I am sorry still not being able to test, because I still dont have any fact table.  I am modeling for a dimensional table.

Thank you..

Regards,

Ratna