Import objects from SSAS to Business Intelligence Development Studio

Hi all,

Could anyone tell me how I can use Business Intelligence Development Studio to browse or even modify data source views, cubes, etc. on SSAS created by means other than the Studio?

Thanks,

hz

Two ways:

1. BI Dev Studio online mode:
Start BI Dev Studio . File->Open->Analysis Services Database.

2. Create new project based on the live version of the database.
Start BI Dev Studio . File->New Project and choose project type "Import Analysis Services 9.0 Database"

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

|||

Hi Edward, your answer is very helpful. Thanks a lot!

If you don't mind, I have one more question regarding this.

If I modify the objects of SSAS by means other than BI Studio. Can BI studio detect these changes and modify its projects accordingly. This is a sort of the reverse process of project deployment.

hz

|||

Unfortunately the answer is: there is no easy way to compare an existing project to the live version of the database.

This is one of the things the next version of the product could be addressing.

For now there are several workarounds I can think of:

1. Export live version of the database into a new project and then use some sort of XML comparison tool to compare project files. Compare .dim files for dimensions, .cube file for cube definitions

2. Create a script of the live version of the database by using right click->Script Database as ... in the SQL Management studio.
And then compare the script to the script you get by running Deployment Wizard utility for your project.
Again you should be able to use XML compare to compare both scripts.

These are not perfect ways, but should give you some idea for extent of the changes done to your live database.

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

|||

Thanks again, Edward! You have offered all I need to know regarding this.

I use AMO to create and modify objects such as cubes, data mining structures. Without using BI Studio, it will be very difficult to check whether my code has created what I need. Now, I'll just import the objects to the studio each time I want to check them.

hz

Import objects from SSAS to Business Intelligence Development Studio

Hi all,

Could anyone tell me how I can use Business Intelligence Development Studio to browse or even modify data source views, cubes, etc. on SSAS created by means other than the Studio?

Thanks,

hz

Two ways:

1. BI Dev Studio online mode:
Start BI Dev Studio . File->Open->Analysis Services Database.

2. Create new project based on the live version of the database.
Start BI Dev Studio . File->New Project and choose project type "Import Analysis Services 9.0 Database"

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

|||

Hi Edward, your answer is very helpful. Thanks a lot!

If you don't mind, I have one more question regarding this.

If I modify the objects of SSAS by means other than BI Studio. Can BI studio detect these changes and modify its projects accordingly. This is a sort of the reverse process of project deployment.

hz

|||

Unfortunately the answer is: there is no easy way to compare an existing project to the live version of the database.

This is one of the things the next version of the product could be addressing.

For now there are several workarounds I can think of:

1. Export live version of the database into a new project and then use some sort of XML comparison tool to compare project files. Compare .dim files for dimensions, .cube file for cube definitions

2. Create a script of the live version of the database by using right click->Script Database as ... in the SQL Management studio.
And then compare the script to the script you get by running Deployment Wizard utility for your project.
Again you should be able to use XML compare to compare both scripts.

These are not perfect ways, but should give you some idea for extent of the changes done to your live database.

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

|||

Thanks again, Edward! You have offered all I need to know regarding this.

I use AMO to create and modify objects such as cubes, data mining structures. Without using BI Studio, it will be very difficult to check whether my code has created what I need. Now, I'll just import the objects to the studio each time I want to check them.

hz

import database from 7 to 2000 but SP and views not come

Hi All,
I am trying to import database from SQL7 to SQL2000 using the tool.
it looks normal but no SPs and Views and transfered and those not even in
the list when I run the transfer tool.
and idea?
TIA
Jerry Qu
Jerry,
You have to make sure you check those objects or they won't be transferred.
If you want the whole database try restoring a full backup. It's easier and
cleaner.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Jerry Qu" <jqu@.capdex.com> wrote in message
news:OhQD9KpdEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am trying to import database from SQL7 to SQL2000 using the tool.
> it looks normal but no SPs and Views and transfered and those not even in
> the list when I run the transfer tool.
> and idea?
>
> TIA
> Jerry Qu
>

import database from 7 to 2000 but SP and views not come

Hi All,
I am trying to import database from SQL7 to SQL2000 using the tool.
it looks normal but no SPs and Views and transfered and those not even in
the list when I run the transfer tool.
and idea?
TIA
Jerry QuJerry,
You have to make sure you check those objects or they won't be transferred.
If you want the whole database try restoring a full backup. It's easier and
cleaner.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Jerry Qu" <jqu@.capdex.com> wrote in message
news:OhQD9KpdEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am trying to import database from SQL7 to SQL2000 using the tool.
> it looks normal but no SPs and Views and transfered and those not even in
> the list when I run the transfer tool.
> and idea?
>
> TIA
> Jerry Qu
>

import database from 7 to 2000 but SP and views not come

Hi All,
I am trying to import database from SQL7 to SQL2000 using the tool.
it looks normal but no SPs and Views and transfered and those not even in
the list when I run the transfer tool.
and idea?
TIA
Jerry QuJerry,
You have to make sure you check those objects or they won't be transferred.
If you want the whole database try restoring a full backup. It's easier and
cleaner.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Jerry Qu" <jqu@.capdex.com> wrote in message
news:OhQD9KpdEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am trying to import database from SQL7 to SQL2000 using the tool.
> it looks normal but no SPs and Views and transfered and those not even in
> the list when I run the transfer tool.
> and idea?
>
> TIA
> Jerry Qu
>

Import Data Problem

Hi all...
I am trying to copy all tables, views, and stored procedures (except for only
one table the user does not want copied) from a database on a remote server to
another database on a local server. Every time I try to run this copy procedure,
it runs for a while and the throws the following error:
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
'Local_Table_Name'.
The source tables have multiple relationships defined to multiple fields in each
table, and I think this is where it is failing.
What can I do to work around this to get the object copied?
Thanks!
Do you get this error when you try to create the script or when you run the
script?
If you get it when you run the script you have a dependency problem. You
will have to investigate when the problem occurs and order these elements
of your script accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:%235fjmbdFFHA.3616@.TK2MSFTNGP10.phx.gbl...
> Hi all...
> I am trying to copy all tables, views, and stored procedures (except for
only
> one table the user does not want copied) from a database on a remote
server to
> another database on a local server. Every time I try to run this copy
procedure,
> it runs for a while and the throws the following error:
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
> 'Local_Table_Name'.
> The source tables have multiple relationships defined to multiple fields
in each
> table, and I think this is where it is failing.
> What can I do to work around this to get the object copied?
> Thanks!
|||I just modify and click through the Import Data Wizard, then click the button to
make it go, and sit back and wait. It shows progress, then after a loooong while
(because this is a large database), the error I described pops up. I also have a
DTS package built that I can alter and run. It only has the one task "Copy SQL
Server Objects". So, I am not quite sure what you mean by "order these elements."
Hilary Cotter wrote:
> Do you get this error when you try to create the script or when you run the
> script?
> If you get it when you run the script you have a dependency problem. You
> will have to investigate when the problem occurs and order these elements
> of your script accordingly.
>
|||take the problem table out and try to DTS it again, unfortunately this will
mean you are starting from scratch again. So you might want to find out what
tables have been successfully exported and then only DTS the tables which
are missing.
You might want also want to look at backing up the database and restoring it
on the destination server.
The problem is SQL Server uses a table called sysdepends to track
dependencies. This table can get out of sync by various database
operations - like alter proc statements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:exNXBHfFFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I just modify and click through the Import Data Wizard, then click the
button to
> make it go, and sit back and wait. It shows progress, then after a loooong
while
> (because this is a large database), the error I described pops up. I also
have a
> DTS package built that I can alter and run. It only has the one task "Copy
SQL
> Server Objects". So, I am not quite sure what you mean by "order these
elements."[vbcol=seagreen]
> Hilary Cotter wrote:
the[vbcol=seagreen]
elements[vbcol=seagreen]

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
Thanks
Most likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
ThanksMost likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
ThanksMost likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>