Copy cube from a DB to another one

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Find the last record by date in a sub group of records. If you are using SQL Server 2000, you might be able to do something like: declare @xample table ( PollId integer, CoveragId integer, EffDate datetime, Status char(1), Limit1 integer ) insert into @xample select 2, 1, '9/7/2007', 'a', 10000 union all select 2, 2, '9/7/2007', 'a', 15000 union all select 2, 2, '10/1/2007','a', 20000 union all select 3, 1, '9/7/2007', 'a', 10000 select pollId, cast(left(tVector,11) as integer) as CoveragId, convert(datetime, substring(tVector, 13, 23)) as EffDate, substring(tVector, 37, 1) as Status, cast(substring(tVector, 39, 11) as integer) as Limit1 from ( select pollId, max ( right(' ' + convert(varchar(11), coveragId), 11) + ' ' + convert(char(24), EffDate, 121) + Status + ' ' + convert(char(11), Limit1) ) as tVector from @xample group by pollId ) a /* pollId CoveragId EffDate Status Limit1 ----------- ----------- ------------------------ ------ ------- 2 2 2007-10-01 00:00:00.000 a 20000 3 1 2007-09-07 00:00:00.000 a 10000 */ Tag: Copy cube from a DB to another one
  - 2
    - Non-additive measures What I suggested was to leave all other (additive) measures in the measure group that you already created. Based on your example, the granularity of Rates is at least Account and Time (but possibly other attributes). You need to create NEW measure group and include just these two dimensions (unless rates vary by other dimensions as well). You didn't specify how the rates should aggregate across accounts, but probably they don't at all. Tag: Copy cube from a DB to another one
  - 3
    - Reporting errors & output from SQL Agent I'm scheduling a T-SQL job with SQL Agent. The job uses PRINT to write out various progress and status messages as it runs. I created an Operator, and set a Notification when the job fails -- will that send me the entire output of the job, or just the error message Tag: Copy cube from a DB to another one
  - 4
    - MSSQLServer logging many missing stored procedure messages /bump Tag: Copy cube from a DB to another one
  - 5
    - VERY slow stored procedure performance on datetime parameter A couple of suggestions: Use SQL Profiler to capture the Plans for the different styles of invocation. The Showplan All and Showplan XML events are under the Performance group. This should let you see any difference in the plans between the fast and slow versions. Try rearranging the procedure arguments to see whether it is the datetime parameters or the parameters at position 11 or whatever that are the problem. It may be that there is some strange boundary effect at the point these parameters are in the list. Were there any changes to the procedure or the tables around the time the problem arose Also are you saying that if you have a local variable and assign the parameter value to that and use the variable it is okay , but if you use the parameter directly then it is slow If this is the true it seems to suggest a difference between their storage/access. I'm not sure what we can do about this, though in this case you have at least found a workround with the variable. Tag: Copy cube from a DB to another one
  - 6
    - search for last non-null field in a row? Not quite a elegant. You have to do the "Unpivot" manually. Code Snippet create table #old ( id int , date datetime , c1 int , c2 int , c3 int , c4 int ) insert into #old values ( 1 , getdate ()- 10 , null, 10 , 20 , null) insert into #old values ( 2 , getdate ()- 9 , 1 , 2 , 3 , null) declare @id int , @dt datetime , @maxcol varchar ( 3 ) set @id = 1 set @dt = ( select date from #old where id = @id ) set @maxcol = 'c4' create table #legacydata ( id int , date datetime , Data int , col varchar ( 10 ) ) insert into #legacydata select id , date , c1 , 'c1' from #old union all select id , date , c2 , 'c2' from #old union all select id , date , c3 , 'c3' from #old union all select id , date , c4 , 'c4' from #old delete #legacydata where data is null select top 1 * from #legacydata where id = @id and date = @dt and col < @maxcol order by col desc depending on the size of the data you're dealing with you may need a primary key on the id/date fields in the #legacydata table. Tag: Copy cube from a DB to another one
  - 7
    - Help with query I'm not sure if this is the right forum but here goes: I want to make a query that selects data from multiple tables and joins it all together. I have that but what i want to do is only select the data I need. at the moment it is returning columns that are not necessary. I'm trying to do something like this: Code Snippet SELECT sysdba.OPPORTUNITY.OPPORTUNITYID AS OPPID FROM sysdba.OPPORTUNITY INNER JOIN sysdba.C_OPPTYINFO ON sysdba.OPPORTUNITY.OPPORTUNITYID = sysdba.C_OPPTYINFO.OPPORTUNITYID For some reason the Inner Join is not joining the two tables. Any one have any suggestions Thanks in advance for the help. Tag: Copy cube from a DB to another one
  - 8
    - Can't install sql server 2005 Did anybody get a solution on this. I am facing the same problem. I tried to open using windows explorer and it first throws javascript error and then it shows the Splash Screen on selecting install server components it throws a message saying. "Please go to Control panel to install and configure system components." Any help is appreciated. Tag: Copy cube from a DB to another one
  - 9
    - ReportServicesConfigUI.WMIProvider.WMIProviderException: System.Runtime.InteropServices.COMException (0x8000000A) Hi there... I did install the Sp2 to, but that didn't quite resolve everything. Know it's working fine... i had a great help in http://www.learnreportingservices.com/ It's a great place, with videos teaching us how to solve a lot of problems... Thanks Best regards Rui Tag: Copy cube from a DB to another one
  - 10
    - Creating a Sum for the Year Looks like you're missing the [Date] column name before the BETWEEN keyword in that second excerpt. Tag: Copy cube from a DB to another one
  - 11
    - New User - Unable to connect I'm getting the same problem trying to connect from my local machine - is this the right solution for me I can actually connect from code ... the following connection string works ... SSLCon = new SqlConnection ( @"Server=(local)\SSLMJ;Integrated Security = True;" + "Database=SSLTESTRESULTS" ); I can drop a sqldatasource onto my page and try to configure it - nothing in the server list ! So - my code can connect but VS can't. What am I missing Tag: Copy cube from a DB to another one
  - 12
    - problem deploying SSIS to another server. Hello, I am trying to deploy a SSIS package (using deployment utility create at build time) to a different server and ran into problem wonder if someone and direct me to the right path. development environment: WIN XP, SQL 2005. package is built using server buisiness intelligence. Package content: connect to a "target"SQL2005server using OLE DB using SQL Login authentication to process data. The SQL 2005 DB is on a Win 2003 server (R2) as OS platform. The package was built, install ( SQL not file system)and ran fine on the development environement; when attempt to deploy the package to the SQL 2005 server where data reside and should be processed (also install to SQL), I receive error " 0x80004005 ...description TCP Provider: No connection could be made because the target machine activily refuse it". which is an odd error since the package is on the server that it try to connect to the data base on it. If anyone can give me some hint at what cause this problem, appreciate the help. Tag: Copy cube from a DB to another one
  - 13
    - Check remotely if Reporting Services exist You can check using http://servername/reportserver or http://servername/reports Tag: Copy cube from a DB to another one
  - 14
    - File Move Component Error From what I've worked on, I've haven't had any issues with the source and destination files being named the same. There is an option to overwrite the destination file in your File Transfer component so that usually overcomes this issue. If you set this Overwrite option to True, it should solve this. Tag: Copy cube from a DB to another one
  - 15
    - Error deploying large Report Model Hi, I also want to know the location of web.confing if i am doing right!! on my system i have web.config and web.config.comment. and there is only MaxRequestLenght in .comment file and i updated that to 102400!! also the execution time out to 1200: the location is: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 Thanks Tag: Copy cube from a DB to another one
  - 16
    - Barcode doesn't print from reports server I do have the data printing in 3 columns but only if I export it to PDF first. The preview only shows one column. My alignment is off vertically. It starts out fine but after the 3rd row its off. Thanks for any help! Tag: Copy cube from a DB to another one
- Next
  - 1
    - How to change the instance name? Hello DBA's I have installed SQL Server 2005 on a machine with named instance, but later I noticed that I created it with the wrong name than desired. Now I need to change the instace name of the SQL Server that I have installed. How can I do that Thanks Satya Tag: Copy cube from a DB to another one
  - 2
    - Database size allocation - where other than tables, logs, etc ???? I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm DB is generally small. ~25 tables, 100 SPs, 10 views, etc. Note: I have 4 queues using SQL Notifications, but when selecting from them results in no data. Thanks Scott Tag: Copy cube from a DB to another one
  - 3
    - SSAS 2005 Connection SSAS only uses windows authentication, there is no such thing as "SQL Server Analysis Services authentication". It does not use SQL server logins, they only relate to the relational engine, not to SSAS. Your windows account needs to be in a database role that has access to the OLAP database. If you cannot connect from SSMS, there is no way your .net application will be able to connect This article is a good one for troubleshooting connection issues http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/cisql2005ascs.mspx Tag: Copy cube from a DB to another one
  - 4
    - How to run DTS package/upgrade in SQL 2005. Thank you for quick response. When Open or Import package by right clicking on "DTS 2000 Packages" folder, it throws "Could not load or assembly Microsoft.SqlServer.Dts80,......." error. I have installed June CTP. I tried installing on another computer, I get same error. Thx, Satish Patil Tag: Copy cube from a DB to another one
  - 5
    - TSQL Challenge Views do not allow the "DECLARE" keyword. When the amendment is effective the data from the amendments table will be selected instead of the contract data . Amendments table preserve the historical changes of the Contract. It should have 2 outputs, 1 view and 1 stored procedure. View: Current_Contracts - This is the list of contracts on its current state(with effective amendment). Stored Proc.: GetContractByDate(Date) - This is the current contract on the time of the given date. Tag: Copy cube from a DB to another one
  - 6
    - Accesing offline cubes with MS Excel 2003 Hi, I created offline cube (.cub file) with SQL Server 2005 Analysis Services. Accessing the offline cube with MS Excel 2003 I get an error message: "Unable to obtain list of tables from the data source". I can access the offline cube with MS Excel 2003 only when MS Excel 2007 is instaled. After uninstaling MS Excel 2007 the same error ocures. I have MS Excel 2003 (11.8146.6568) SP2, Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider, Microsoft Core XML Services (MSXML) 6.0 instaled. Thanks for help, Martin Tag: Copy cube from a DB to another one
  - 7
    - Disaster Recovery - How to verify my 2005 standard databases using 2005 express Do a live.com search on "SQL Server Orphaned Accounts". Tag: Copy cube from a DB to another one
  - 8
    - How can we create cubs in sql server 2005 BI The link is correct. Data source views are a feature only in Analysis Services 2005. Here is the link to all articles about Analysis Services. http://www.databasejournal.com/features/article.php/3593466 Look for the writer William E. Pearson Here is the link to Books On Line Analysis Services 2005 tutorials: http://msdn2.microsoft.com/en-us/library/ms170208.aspx Regards Thomas Ivarsson Tag: Copy cube from a DB to another one
  - 9
    - Can't Get SQL Authentication to work With XP Pro SP2 in very simple test case I think there was something in the decision tree that I missed... As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication" After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it). If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app." Long way to the first u-turn....hazards of being a newbit! Tag: Copy cube from a DB to another one
  - 10
    - Using Array Variable in ForEach Loop Container Hery Susanto WR wrote: Thanks Ravi, it helped me so much, now I know what the "Traverse subfolders" is for :) Is to get to the subfolders; in your case that is required as SSIS needs to go all branch_nm subfolders. Hery Susanto WR wrote: One more question Ravi, when I put the value into variable, can I get that value inside Script Component my plan is establish the flat file connection inside Script Component and get the data row by row using Synchronous method and push it into database. Is that a problem You could; but the dataflow has already all that functionality built-in. Inside of the ForEach loop container, place a dataflow. Then in the dataflow, use a flat file source component and the required destination component. The trick is to use an expression in I have an example that shows how to do it for excel files. See an example here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx Tag: Copy cube from a DB to another one
  - 11
    - Database corrupt on power loss Ilya, I disabled the cache write for the drive. I will restart test and hopefuly this is the solution. Thanks again! Tag: Copy cube from a DB to another one
  - 12
    - tfs installation issue !!!!!!!!1 i had SP1 of visual studio and TFS installed on my server but it didnt work and i had a server failure, so i removed the SP1 after doing this i had this error: " Team Foundation Server does not exist or is not accessible at this time. Technical information (for administrator): HTTP code 503: TF30059: Fatal error while initializing web service " after searching on line i found that there is problem in version number of Microsoft.TeamFoundation.VersionControl.Server.dll file so i changed it to be compatible with TFS version number. by dowing this the following erro appeared " Please contact your administrator. There was an error contacting the server. Technical information (for administrator): HTTP code 500: Internal Server Error " after that i removed the TFS and tried to reinstall it on the same server, here i had the following error: "Error 28922.TFServerStatusValidator: ErrorConnectingToDatabaseWhenImpersonated - Most likely the service account doesn't have permission to access TFS database (because the tool succeeded connecting to the TFS database as the current user - user running setup)" and here im totaly stuck , coz the user of services can connect to sql server , i used the sevice account to connect to sql server and connection was established successfully. Tag: Copy cube from a DB to another one
  - 13
    - Connection Error from ASP.Net under IIS: provider: Named Pipes Provider, error: 40 Does your connection string like "servername\instancename" or something else The TCP failure indicates either the server TCP listener is not up or client uses different TCP port to connect. So please check whether the TCP is enabled or not. Restart the server after the configuration change. Then check if the connection string has instancename specified. Once you find out which port the server is listening on, you can also use "tcp ervername,port" to connection to the server. Tag: Copy cube from a DB to another one
  - 14
    - Cant restore the database backup in SQL server 2005? Hi, System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 83679641600 additional free bytes, while only 46151348224 bytes are available. (Microsoft.SqlServer.Smo) Thanks, S.Shyam. Tag: Copy cube from a DB to another one
  - 15
    - How to move Full Text Catalog SQL 2005??? You can find the answer here: http://msdn2.microsoft.com/en-us/library/ms345483.aspx Just scroll down to the section regarding "Moving Full-Text Catalogs" Good luck! Tag: Copy cube from a DB to another one
  - 16
    - How to programmatically set column mappings of a simple data flow task? Donny Ma wrote: Thanks, Jamie, that helped a lot. I have a further question, though. I want to make a script task that will programmatically set the mappings in the subsequent data flow task. Can't be done. Period. Read more from the father of SSIS, Kirk Haselden: Self Modifying Packages in SSIS ( http://sqljunkies.com/WebLog/knight_reign/comments/17731.aspx ) -Jamie Tag: Copy cube from a DB to another one

Juan64

Hi,

I would like to copy a cube and its content from a database to another one.

The first database is the one used for production and the second will be used to archive the old cubes.

Is there a specific way to deal with this kind of issue

Thanks in advance for your support.

Rgds,

Juan

Re: Copy cube from a DB to another one

Thomas Ivarsson

Hello! You have not told about what version you are using, AS2000 or SSAS2005

With SSAS2005 you simply change this is the properties of the project and deployment.

This is in the solution explorer of BI-Developer Studio.

In AS2000 you can use archive and restore from one machine to another as long as the data source name is the same.

Regards

Thomas Ivarsson

Re: Copy cube from a DB to another one

C.F

In SSAS 2005, you also can try the new feature 'synchronize'.

Re: Copy cube from a DB to another one

Juan64

I am using SSAS 2005. Can you tell me more about the "synchronize" feature.

Also, is there a way using xmla scripts

Cheers,

Juan

Re: Copy cube from a DB to another one

C.F

You can take a look at the following link for "synchronize".

http://msdn2.microsoft.com/en-us/library/ms174928.aspx

Yes. It supports xmla script. The easy way is to generate the script using the synchronize wizard.

Re: Copy cube from a DB to another one

Juan64

Thx !