Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

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

|||

hello,

How do i stack those hierarchy?

thanks

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

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

|||

hello,

How do i stack those hierarchy?

thanks

sqlsql

collation problems

hello, i'm restoring a backup of a database of other sql server
instance, but this database has the collation: modern_spanish_cl_as,
the collation of my sql server is: sql_latin_general_cp1ci_as, its
posible change the collation of the database that i restoring
(modern_spanish_cl_as)?
thanksYou don't need to change Collation for restoring the database. SQL Server
will allow you to restore a database of modern_spanish_cl_as Collation on the
server with sql_latin_general_cp1ci_as Collation. Here is related information
from BOL (collations, multiple):
Collations in BACKUP and RESTORE
If you restore a database, RESTORE uses the collation of the source database
that was recorded in the backup file. The restored database has the same
collation as the original database that was backed up. Individual objects
within the database that have different collations also retain their original
collation. The database can be restored even if the instance on which you run
restore has a different default collation than the instance on which BACKUP
was run.
If there is already a database with the same name on the target server, the
only way to restore from the backup is to specify REPLACE on the RESTORE
statement. If you specify REPLACE, the existing database is completely
replaced with the contents of the database on the backup file, and the
restored version of the database will have the same collation recorded in the
backup file.
If you are restoring log backups, the destination database must have the
same collation as the source database.
"hongo32" wrote:
> hello, i'm restoring a backup of a database of other sql server
> instance, but this database has the collation: modern_spanish_cl_as,
> the collation of my sql server is: sql_latin_general_cp1ci_as, its
> posible change the collation of the database that i restoring
> (modern_spanish_cl_as)?
> thanks
>|||hello absar, i don't understand very well... i need to make a database
whit the collation that i want... and over that database restore de
backup... then, the backup take the backup of the 'target' database...
it this right?|||The restored database on the target server will have collation of the source
database. If you create a database with the target's collation to restore
from the backup file, you will have to specify the Replace option in the
command for database restoration. And the 'Replace Option' will ensure that
the 'restored version of the database will have the same collation recorded
in the backup file'.
However, you have the option of changing collation of the restored database.
After restoring the database on the target server, you can use the 'ALTER
DATABASE ...... COLLATE ...' and 'ALTER TABLE...... COLLATE ...'
commands. Please see details in the BOL (collations, changing).
"hongo32" wrote:
> hello absar, i don't understand very well... i need to make a database
> whit the collation that i want... and over that database restore de
> backup... then, the backup take the backup of the 'target' database...
> it this right?|||ok, thanks... i don't understand what you mean whit: BOL... is books
on line... jejejeje... thanks again, bye

Collation Problem Using Openquery to insert record to Oracle

Hi,
I am trying to insert a record into an Oracle 9.2 instance from Microsoft's
SQL Server 2000 using openquery, it is really slow, someone knows if it has
to do with differences between SQL Collation setting and Oracle's NLS_SORT,
my current setings are:
SQL: SQL_Latin1_General_CP1_CI_AS
Oracle NLS_SORT: Binary
I configured the Linked server with the Use Remote Collation option and left
the collation name blank.
Thanks a lot!!
IgnacioHi
You don't say how the oracle server is linked and which drivers/versions you
are using.
Are you getting any error messages?
What are you doing with returned data?
John
"i-DLT" wrote:

> Hi,
> I am trying to insert a record into an Oracle 9.2 instance from Microsoft'
s
> SQL Server 2000 using openquery, it is really slow, someone knows if it ha
s
> to do with differences between SQL Collation setting and Oracle's NLS_SORT
,
> my current setings are:
> SQL: SQL_Latin1_General_CP1_CI_AS
> Oracle NLS_SORT: Binary
> I configured the Linked server with the Use Remote Collation option and le
ft
> the collation name blank.
> Thanks a lot!!
> Ignacio