How to import cube definition from SSAS 2000 to SSAS 2005

Hi.
I have a SSAS project CAB-file created in SSAS2000 and would like to import/convert it to a SSAS2005 project but I can't find any way to do this... Does anybody know how this is done? Any suggestions would be greatly appreciated!

Regards
Kjetil

AS2005 won't read the backup files produced in AS2000, but AS2005 comes with MigrationWizard.exe that can be used. You will need to:
- have AS2000 running and containing the database you want to migrate
- run MigrationWizard.exe, specify the source AS2000 and then you can decide to migrate directly to an AS2005 or to just generate the migration script (and XML/A script that you can later run on AS2005 with SQL Management Studio)
- you will need to reprocess the database on AS2005

Adrian.

|||Hello Adrian,

I tried your solution but I still have a problem.

In my case the MSAS 2000 (with cubes) is on a separate server. I can connect and start the wizard but when the wizard is checking the data source it fails.

CONFIG:
Server #1: MS SQL Server 2000 (SP4) + MS AS 2000 (SP4)

Server #2: MS SQL Server 2005 + MS AS 2005

I have a local account on Server #1 and a local account on Server #2. Both same name + password.

The DTS-wizard is working fine, but the MSAS migrationwizard not.

Any ideas?

Thanks in advance for any help.
Marco.|||Hi,
The error occurs in the validation page (where there is a tree with the AS2000 objects) or on the next page (migration page - where the script is being sent to AS2005) ?
Can you please post the error ? Or the log if the error occured in the validation page (there are some buttons under the tree, one can be used to get the log).

Thank you,
Adrian Dumitrascu.|||Hello Adrian,

Thanks for your reply.

My first server (SQL 2000), SERVER#1 is on domain AAA.DOMAIN.COM
My second server (SQL 2005), SERVER#2 is on domain BBB.DOMAIN.COM

The message I get is: Source server: The following system error occurred: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Any suggestions?

Why not just easy use the .CAB file?

Thanks in advance,
Marco.|||Thank you!
Sorry for the delayed reply but I'be been rather busy lately.
I am starting on this project next week and will try out your suggestions.

Best regards
Kjetil

How to import cube definition from SSAS 2000 to SSAS 2005

Hi.
I have a SSAS project CAB-file created in SSAS2000 and would like to import/convert it to a SSAS2005 project but I can't find any way to do this... Does anybody know how this is done? Any suggestions would be greatly appreciated!

Regards
Kjetil

AS2005 won't read the backup files produced in AS2000, but AS2005 comes with MigrationWizard.exe that can be used. You will need to:
- have AS2000 running and containing the database you want to migrate
- run MigrationWizard.exe, specify the source AS2000 and then you can decide to migrate directly to an AS2005 or to just generate the migration script (and XML/A script that you can later run on AS2005 with SQL Management Studio)
- you will need to reprocess the database on AS2005

Adrian.

|||Hello Adrian,

I tried your solution but I still have a problem.

In my case the MSAS 2000 (with cubes) is on a separate server. I can connect and start the wizard but when the wizard is checking the data source it fails.

CONFIG:
Server #1: MS SQL Server 2000 (SP4) + MS AS 2000 (SP4)

Server #2: MS SQL Server 2005 + MS AS 2005

I have a local account on Server #1 and a local account on Server #2. Both same name + password.

The DTS-wizard is working fine, but the MSAS migrationwizard not.

Any ideas?

Thanks in advance for any help.
Marco.|||Hi,
The error occurs in the validation page (where there is a tree with the AS2000 objects) or on the next page (migration page - where the script is being sent to AS2005) ?
Can you please post the error ? Or the log if the error occured in the validation page (there are some buttons under the tree, one can be used to get the log).

Thank you,
Adrian Dumitrascu.|||Hello Adrian,

Thanks for your reply.

My first server (SQL 2000), SERVER#1 is on domain AAA.DOMAIN.COM
My second server (SQL 2005), SERVER#2 is on domain BBB.DOMAIN.COM

The message I get is: Source server: The following system error occurred: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Any suggestions?

Why not just easy use the .CAB file?

Thanks in advance,
Marco.|||Thank you!
Sorry for the delayed reply but I'be been rather busy lately.
I am starting on this project next week and will try out your suggestions.

Best regards
Kjetil

How to implement shared member

We are doing a proof of concept to replace Essbase with SSAS. One of the tasks is to create a hierarchy a same member in two places. I know Analysis Services has not supported this feature in the past. Is there anything new in 2005 that would facilitate this?

Example:

Fee Type Dim

All Fee Types

FT1

FT2

FT3

FT4

FT5

FT6

Conversion Fee Types

FT2

FT4

Extension Fee Types

FT5

FT6

Thanks to some good posted hints from Richard Tkachuk, created a work around. So SSAS can't do true shared members well to my understanding neither can Hyerion Essbase in aggregate storeage mode. But what can be done is to create more members in the dimension that have no matching facts. Then write a few calc scripts to associate the measure data with the appropriate real member. Richards example was for a parent child heirarchy. Mine's a little simpler as I only needed a couple of attribute hierarchies. Here's some what I did to help any else that may need it. I'm also not the most experienced MDX coder so there may be a more efficient way to do this.

Changed Dimension Table: added a columns for an associated member, an attribute hierarcy, and a flag in dicating shared member.

CREATE TABLE [dbo].[FEE_TYP](
[FEE_TYP_CD] [char](3) NOT NULL,
[FEE_TYP_DESC] [varchar](62) NULL,
[FEE_TYP_ASSOCIATED] [char](2) NULL, -- Member Key for associated member
[FEE_TYP_SM_FLG] [char](1) NULL, -- Shared Member Flag
[FEE_TYP_HIERARCHY] [varchar](30) NULL) -- Attribute Hierarchy

Sample rows.

insert FEE_TYP values ('96', 'Conversion Refinance /', NULL, NULL, 'All Fee Typ')
insert FEE_TYP values ('A96', '96 - Conversion Refinance /', '96', 'Y', 'All Loan Cnvrt')
insert FEE_TYP values ('B96', '96 - Conversion Refinance /', '96', 'Y', 'Cnvrt Refi')
insert FEE_TYP values ('C96', '96 - Conversion Refinance /', '96', 'Y', 'RT Cnvrt Fee Chng')
insert FEE_TYP values ('D96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Fee Chng')
insert FEE_TYP values ('E96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Move')

Created a hierarchy called All Curr Fee Types

Sample calc:

scope ({[FEE TYP].[All Curr Fee Types].members},[FEE TYP].[FEE TYP SM FLG].&)
this = (StrToMember("[FEE TYP].[FEE TYP].&[" + [FEE TYP].[FEE TYP].currentmember.properties("FEE TYP ASSOCIATED") + "]"))
end scope
scope ({[FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt].children))
end scope
-- repeat for other members of the attribute heirarchy
scope ({[FEE TYP].[All Curr Fee Types].[ALL]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Fee Typ].children))
end scope

How to implement shared member

We are doing a proof of concept to replace Essbase with SSAS. One of the tasks is to create a hierarchy a same member in two places. I know Analysis Services has not supported this feature in the past. Is there anything new in 2005 that would facilitate this?

Example:

Fee Type Dim

All Fee Types

FT1

FT2

FT3

FT4

FT5

FT6

Conversion Fee Types

FT2

FT4

Extension Fee Types

FT5

FT6

Thanks to some good posted hints from Richard Tkachuk, created a work around. So SSAS can't do true shared members well to my understanding neither can Hyerion Essbase in aggregate storeage mode. But what can be done is to create more members in the dimension that have no matching facts. Then write a few calc scripts to associate the measure data with the appropriate real member. Richards example was for a parent child heirarchy. Mine's a little simpler as I only needed a couple of attribute hierarchies. Here's some what I did to help any else that may need it. I'm also not the most experienced MDX coder so there may be a more efficient way to do this.

Changed Dimension Table: added a columns for an associated member, an attribute hierarcy, and a flag in dicating shared member.

CREATE TABLE [dbo].[FEE_TYP](
[FEE_TYP_CD] [char](3) NOT NULL,
[FEE_TYP_DESC] [varchar](62) NULL,
[FEE_TYP_ASSOCIATED] [char](2) NULL, -- Member Key for associated member
[FEE_TYP_SM_FLG] [char](1) NULL, -- Shared Member Flag
[FEE_TYP_HIERARCHY] [varchar](30) NULL) -- Attribute Hierarchy

Sample rows.

insert FEE_TYP values ('96', 'Conversion Refinance /', NULL, NULL, 'All Fee Typ')
insert FEE_TYP values ('A96', '96 - Conversion Refinance /', '96', 'Y', 'All Loan Cnvrt')
insert FEE_TYP values ('B96', '96 - Conversion Refinance /', '96', 'Y', 'Cnvrt Refi')
insert FEE_TYP values ('C96', '96 - Conversion Refinance /', '96', 'Y', 'RT Cnvrt Fee Chng')
insert FEE_TYP values ('D96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Fee Chng')
insert FEE_TYP values ('E96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Move')

Created a hierarchy called All Curr Fee Types

Sample calc:

scope ({[FEE TYP].[All Curr Fee Types].members},[FEE TYP].[FEE TYP SM FLG].&)
this = (StrToMember("[FEE TYP].[FEE TYP].&[" + [FEE TYP].[FEE TYP].currentmember.properties("FEE TYP ASSOCIATED") + "]"))
end scope
scope ({[FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt].children))
end scope
-- repeat for other members of the attribute heirarchy
scope ({[FEE TYP].[All Curr Fee Types].[ALL]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Fee Typ].children))
end scope