Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server. We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
-- James E. Bothamley Senior Database Administrator Dave & Buster's, Inc. 2481 Manana Dallas, TX 75220
Work Phone (214) 904-2296 email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light in the strangest of places if you look at it right"
Hi everyone, Sql express support olap server?, because I try it to make an a Pivot table in Ms Excel, but i can't, then Sql server have this feature?thx.p.d: sorry but my english :P
HI, i am new to OLAP in Sql Server 2005, bascially i have read through lots of article on this OLAP and basically i get to know how it works and function theorically, but i could not find an article to show me step by step on how to create the cube and how to query the data from the cube. Would very much appreciated if somebody out there can explain to me in details how to i create cube from sql server 2005 as i totally no idea at all. Though i can view the sample cude in sql server 2005, but i dunno how to create it from my relational database. I need it very urgent as needed by my job functionality. Hope to get some explanation from you guys asap. Just assume i am totally new and please guide me step by step to create a normal and simple cube and query from the cube. I just need to create simple prototype. Thanks alot yea guys :)
Is anyone satisfied with stability of OLAP-services ?
We´ve got a cube with 103 Levels and about 1.100.000 rows in the fact table.
After processing the cube everything works fine. After changing the fact-table (new rows) the only option is to process the cube (no incremental update or refresh offered). OK. After processing the cube (which sometimes failes) the cube sometimes containes wrong data !? Sometimes during operation with the OLAP-Client(inSight from arcplan) the data changes. Sometimes it is not possible to connect to the cube.
Did anyone have same or other or better experience ? Keen to discuss !
We are running SQL Server 7.0, Service Pack 3, on a NT 4.0 (Service Pack 5a?) server. The server is running both SQL Server and OLAP.
When attempting to connect to the OLAP instance on the NT 4.0 server from a local PC, also running SQL Server 7.0 Service Pack 3, the following error message is displayed:
"Cannot retrieve the repository information from the server [datamartact]."
Note: 'datamartact' is the name of the NT 4.0 server.
Does anyone encountered this message before? Could this mean that the person who is trying to connect neads read or read/write permission to the partition were the repository is kept?
I want register a server it is on other PC is a server, BUT IT SHOW TO ME A MESSAGE OF ERROR: Errors ocurred while connecting to ----ip----- Cannot open connection to Analysis server ----ip----- Can not connect a server ----ip-----. not this in operation or this occupied Do you still Want to register this server
:eek: I am having trouble setting my SQL databases up as analysis services databases in SQL Server '05. Does anyone know how to help me???? I have lots of cubes to build, but I can't get the datasources there. Thanks much for any advice. mbunch@bighammer.com
We are experiencing some contention of resources between our RDBMS and our OLAP environment; both SQL2005; so we are going to put the OLAP side of life onto another seperate box. Therefore I need to work out what sort of requirements we are going to need; and this is where i have no idea.
I have had a look at the HP sizers, however this is looking primarily at normal RDBMS setups rather than specifically for OLAP. Is there any specifics that I need to consider/nail down before working this out?
To give you an idea: We currently have 210 million rows in our main fact table, and each row is averaging about 330 bytes The size of our current OLAP database is only 4GB It appears as though we are currently getting anywhere between 6 and 20-30 concurrent users
Considering the above; I would have thought a dual core cpu setup (maybe x2) with 8-10GB of ram would have been the ticket? Now I have done a server sizing exercise for a normal SQL2005 installation; considering temp db on seperate spindles to the log and database tables etc; but what sort of considerations do I need to do for a server running SSAS?
Thanks in advance for any assistance anyone can offer on this front
I have SQL Server 7 SP1 OLAP Server SP1 installed on two WIN NT 4 SP5 machines.
On machine 1 I can connect to the locally installed OLAP Server, but cannot connect to machine 2's OLAP server. (and vice versa machine 2 cannot connect to machine1's OLAP server)
The error I am getting is the following: "Error occurred while connecting to machine2 Cannot open the following registry key on the server computer(machine2): SOFTWAREMicrosoftOlap ServerServer Connection Info"
Can anyone give me a clue as to what is going on. (note: I have tried the 'default' MS troubleshooting method of re-installing - no change)
All of my client boxes connect nicely to MS OLAP Server except one which fails with the following message: "Cannot connect to the registry on the server computer (servername)" where servername is the name of the server on which OLAP server resides.
I'm trying to register a remote OLAP server under OLAP Manager. After I enter the machine name I get the following:
Errors occurred while connecting to <server>. Cannot open the following registry key on the server computer (<server>): SoftwareMicrosoftOLAP ServerServer Connection Info Do you still want to register this server?
The noticeable thing about this error message is that the registry key is missing the first level (HKEY_....). Has anyone been able to register a remote server? I can run OLAP Manager on the server without a problem. I just uninstalled and reinstalled OLAP services on the server and my desktop. There is a registry key with this path under HKEY_LOCAL_MACHINE on the server.
I try to access an OLAP Server from a remote compute, using ADO MD Library with following string conection : "Datasource=SERVNAME; Provider=msolap; Initial Catalog=FoodMart 2000;" I recive the following error code: -2147467259 (0x80004005), "Unspecified error" Can sombody help me
I am new to SQL Server and not quite understand the difference between OLAP server or SQL Analysis Service. Are they referring to the same thing?? An user asked me to confirm that the OLAP server is active on a server running SQL Server 2005, what do I need to check. The Analysis service is up and running, does it mean it is OK??
I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me). Please help.
I want to connect MS SQL 2000 Server with Crystal Analysis via OLAP.
I have installed SQL with Windows authentication mode and installed crystal Analysis 9.0
I have configured Crystal OLAP connection setup.
When I am trying to open or create new application through CA I am not getting through OLAP connection with SQL and I am receiving the error as the following.
Error 1
" Unknown User name and bad password."
Error 2
Failes to initialize.( Cannot connect to the server 'XXXX." the server is not started or too busy)
I recently wrote a white paper on OLAP and was having a hard time finding any information about what was new in SQL Server 2000 OLAP on the web. I didn't really find much until I downloaded a trial copy of SQL Server 2000 (which didn't have OLAP, but had links to information on it). We use Hyperion Essbase, and I was wondering if Microsoft OLAP 2000 would be a viable competitor.
1. Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my Data Warehouse is changed (Having Append Data) - My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project....
I would like to know if Reporting Services 2005 is a good tool for making reporting activities on SQL Server 2005 DW and also using OLAP (Analysis Services 2005).
How to add a task to copy an OLAP database to a remote Sql server in control flow using SSIS 2005? This OLAP database is existed in the remote server and I just want to replace the existing one with the one just been updated in the SSIS package and the last task in the package is to copy the updated OLAP database and replace the one at the remote server. Hopfully during the copy and paste process it will not screw up the reports that are using the OLAP cubes at the remote server as the report data source. Thanks.
Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP?
It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects. I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000. the DW tool vendor will not support an upgrade to SSAS 2005. We wish to extend the DW from other data sources. My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box.
I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package. Any ideas?
We are getting symptoms of a 30 second timeout irrespective of what we do, but only on certain machines. Can anyone shed any light on how the connection timeout can be set, as there seems to be a factor overiding any settings we set?
We are working in Excel (2003) connecting to an Analysis Services 2005 cube through a website https://Datasource connection. When we edit the connection string that the file is using (either by forcing use of an exterior .ODC file and editing the settings in there, or by converting the xls spreadsheet into an xml spreadsheet and manually editing the connection string in there) we get no joy: after 30 seconds the error appears:
"Either a connection cannot be made to the [my server address], or Analysis services is not running on the computer specified".
This error is itself rubbish: If a smaller query (one that takes < 30 seconds) is run in the same Excel file and Pivot Table, it connects fine. What really doesn't make sense is that the exact same file works on some machines and not others. These machines are all inside the same network (and the machines that work have been proven to work on a number of other networks). What's more there is no pattern to the machine capabilities - some newer / faster machines get the error. These machines (that work and do not) have excel 2003 SP3. (Another of the ones that works has Excel 2007).
Changing the connection string to include "Timeout=300" or "Connect Timeout=300" makes no difference, and "Command Timeout" causes a different error so that it wont even attempt to run the query (as opposed to collapsing after 30 seconds of normal progress). Similarly in the .ODV the tags <ODC:CommandTimeout>, <CommandTimeout>, <ODC:Connect Timeout>, <ODC:Command Timeout>, <meta name=CommandTimeout content=1000> etc. have no effect (the file still gets the 30 seconds error rather than just giving up so no way of working out correct syntax)
The only pattern we can see, and we can't fix this, is that the machines that work have Retail versions of excel, and the ones that do not have corporately licensed versions of excel. We cannot see any difference between them in the Help >Abouts, but thought it might be worth mentioning.
Any help would be fantastically appreciated, we are out of ideas. Thanks kindly
Hi there, Here we have got a asp.net application that was developed when database was sitting on SQL server 6.5. Now client has moved all of their databases to SQL server 2000. When the database was on 6.5 the previous development team has used oledb connections all over. As the databases have been moved to SQL server 2000 now i am in process of changing the database connection part. As part of the process i have a login authorization code. Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean Dim conn As IDbConnection = GetConnection() Try Dim cmd As IDbCommand = conn.CreateCommand() Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation" 'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql cmd.CommandType = CommandType.StoredProcedure NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username) NorisHelpers.DBHelpers.AddParam(cmd, "@password", password) conn.Open() 'Get string for return values Dim ReturnValue As String = cmd.ExecuteScalar.ToString 'Split string into array Dim Values() As String = ReturnValue.Split(";~".ToCharArray) 'If the return code is CONTINUE, all is well. Otherwise, collect the 'reason why the result failed and let the user know If Values(0) = "CONTINUE" Then Return True Else results.Result = Values(0) 'Make sure there is a message being returned If Values.Length > 1 Then results.Message = Values(2) End If Return False End If Catch ex As Exception Throw ex Finally If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then conn.Close() End If End Try End Function ''' ----------------------------------------------------------------------------- ''' <summary> ''' Getting the Connection from the config file ''' </summary> ''' <returns>A connection object</returns> ''' <remarks> ''' This is the same for all of the data classes. ''' Reads a specific connection string from the web.config file for the service, creates a connection object and returns it as an IDbConnection. ''' </remarks> ''' ----------------------------------------------------------------------------- Private Function GetConnection() As IDbConnection 'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON) Return conn End Function in the above GetConnection() method i have commented out the .net dataprovider for oledb and changed it to .net dataprovider for SQLconnection. this function works fine. But in the authenticate method above at the line Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error. Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate. If i comment out the Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection and uncomment the .net oledb provider, Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection then it works fine. I also have changed the webconfig file as below. <!--<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";' />--> <add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"... The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
I get the following error and have been trying to figure out why I keep getting it. Initially, I had placed my project under wwwroot folder and ran it under IIS and it gave this error. Then I moved it to my local C drive and same thing. I am sharing this project with two other co-workers and all our config files and code files are same...they don't get this error but I do. I checked that SQL Server Client Network Utility has TCP/IP and the 'Named Pipes' enabled. I thought maybe I have setting in the Visual Studio 2005 that I'm not aware of that's causing this problem...it can't be the server since my co-workers aren't having this error and can't be anything in the code since all of us are sharing this project through vss. I dont' think using different version of .net framework can create this error. I changed the version from 2.0 to use 1.1x and it gave same error... Any help would be greatly appreciated. Thanks in advance.
Server Error in '/RBOdev' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +685966 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +109 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +383 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() +16 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) +195 System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) +95 System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() +105 System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) +497 System.Web.UI.Control.InitRecursive(Control namingContainer) +321 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider,
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)I tried working out solutions from various websites. But the no solution is effective. Could anyone help me in solving this issue.
When my default.aspx page loads I get this error for the connection to my SQL 2005 DB. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I have already gone to Surface Area Configuration to ensure that Remoting with both Named Pipes or TCP/IP is enabled and it's set to both for remoting.I verified that my local account has access to the DB.Not sure what else to check.
could anyone guide me on how to allow, ceate rule for sql server 2005 remote connections to work. i already configured my sql server to allow remote connections and also my router, it is only in isa that is blocking the connections.