SSAS Tabular cube no longer working

Question

SSAS Tabular cube no longer working

richardmgreen1

SSChampion

Points: 10356
More actions
April 30, 2018 at 4:12 am

#345029

Hi all
I've got a tabular cube (SSAS 2016) that was working on Friday.
I want to add another field to the cube and I've gone into Table Properties and clicked the Design button.
This shows me the query that's being run ("SELECT [dbo].[Fact_APC_Theatres_Times].* FROM [dbo].[Fact_APC_Theatres_Times]").
If I click run I get around 50 (or so) records with the new column in it. This looks like a preview. There are about 80k records in this table in total with no partitions as yet.
I click OK to exit that screen screen and OK again to process the table (and bring the new column into the model) and I get the following error:-
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
A connection could not be made to the data source with the Name of 'DataWarehouse'.
'.
I know the database is on-line and usable as I can select from it in Management Studio and the basic "Run" from table designer in SSAS works (see above).
The table in the database looks like this:-
USE [DataWarehouse] GO
/****** Object: Table [dbo].[Fact_APC_Theatres_Times] Script Date: 30/04/2018 11:07:13 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Fact_APC_Theatres_Times]( [CaseID] [varchar](15) NOT NULL, [SpellLocalID] [varchar](24) NOT NULL, [TheatreCaseTimesKey] [bigint] NULL, [TheatreLocationKey] [int] NOT NULL, [SessionClinicanKey] [int] NOT NULL, [SessionConsultantKey] [int] NOT NULL, [SessionSurgeonKey] [int] NOT NULL, [SessionAnaesthetistKey] [int] NOT NULL, [CaseSurgeonKey] [int] NOT NULL, [SessionStartDateKey] [int] NOT NULL, [SessionStartTimeKey] [int] NOT NULL, [SessionEndDateKey] [int] NOT NULL, [SessionEndTimeKey] [int] NOT NULL, [CancellationReasonKey] [int] NOT NULL, [CaseTypeKey] [int] NOT NULL, [ScheduledDateKey] [int] NOT NULL, [ScheduledTimeKey] [int] NOT NULL, [ActualDateKey] [int] NOT NULL, [ActualTimeKey] [int] NOT NULL, [PatientReadyForCallingDateKey] [int] NOT NULL, [PatientReadyForCallingTimeKey] [int] NOT NULL, [TheatreCalledPatientDateKey] [int] NOT NULL, [TheatreCalledPatientTimeKey] [int] NOT NULL, [EscortGoneForPatientDateKey] [int] NOT NULL, [EscortGoneForPatientTimeKey] [int] NOT NULL, [PatientIntoTheatreReceptionDateKey] [int] NOT NULL, [PatientIntoTheatreReceptionTimeKey] [int] NOT NULL, [PatientIntoAnaestheticRoomDateKey] [int] NOT NULL, [PatientIntoAnaestheticRoomTimeKey] [int] NOT NULL, [AnaesthetistTreatingPatientDateKey] [int] NOT NULL, [AnaesthetistTreatingPatientTimeKey] [int] NOT NULL, [PatientReadyForSurgeonDateKey] [int] NOT NULL, [PatientReadyForSurgeonTimeKey] [int] NOT NULL, [PatientReadyForTheatreDateKey] [int] NOT NULL, [PatientReadyForTheatreTimeKey] [int] NOT NULL, [StartOfCaseDateKey] [int] NOT NULL, [StartOfCaseTimeKey] [int] NOT NULL, [PatientIntoTheatreDateKey] [int] NOT NULL, [PatientIntoTheatreTimeKey] [int] NOT NULL, [AnaestheticInductionDateKey] [int] NOT NULL, [AnaestheticInductionTimeKey] [int] NOT NULL, [PatientReadyForProcedureDateKey] [int] NOT NULL, [PatientReadyForProcedureTimeKey] [int] NOT NULL, [StartOfProcedureDateKey] [int] NOT NULL, [StartOfProcedureTimeKey] [int] NOT NULL, [KnifeToUterusDateKey] [int] NOT NULL, [KnifeToUterusTimeKey] [int] NOT NULL, [SuturingBegunDateKey] [int] NOT NULL, [SuturingBegunTimeKey] [int] NOT NULL, [EndOfProcedureDateKey] [int] NOT NULL, [EndOfProcedureTimeKey] [int] NOT NULL, [RecoveryRequestedDateKey] [int] NOT NULL, [RecoveryRequestedTimeKey] [int] NOT NULL, [PatientLeftTheatreDateKey] [int] NOT NULL, [PatientLeftTheatreTimeKey] [int] NOT NULL, [PatientRecoveryStartedDateKey] [int] NOT NULL, [PatientRecoveryStartedTimeKey] [int] NOT NULL, [PatientFitToLeaveRecoveryDateKey] [int] NOT NULL, [PatientFitToLeaveRecoveryTimeKey] [int] NOT NULL, [WardNotifiedDateKey] [int] NOT NULL, [WardNotifiedTimeKey] [int] NOT NULL, [PatientLeftRecoveryDateKey] [int] NOT NULL, [PatientLeftRecoveryTimeKey] [int] NOT NULL, [PatientIntoDSCWardDateKey] [int] NOT NULL, [PatientIntoDSCWardTimeKey] [int] NOT NULL, [PatientLeftDSCWardDateKey] [int] NOT NULL, [PatientLeftDSCWardTimeKey] [int] NOT NULL, [TheatresOutcome] [int] NOT NULL, [PatientReadyForCallingDelayReasonKey] [int] NOT NULL, [PatientReadyForCallingDelayTime] [int] NOT NULL, [TheatreCallPatientDelayReasonKey] [int] NOT NULL, [TheatreCallPatientDelayTime] [int] NOT NULL, [EscortGoneForPatientDelayReasonKey] [int] NOT NULL, [EscortGoneForPatientDelayTime] [int] NOT NULL, [PatientIntoTheatreReceptionDelayReasonKey] [int] NOT NULL, [PatientIntoTheatreReceptionDelayTime] [int] NOT NULL, [PatientIntoAnaestheticRoomDelayReasonKey] [int] NOT NULL, [PatientIntoAnaestheticRoomDelayTime] [int] NOT NULL, [AnaesthetistTreatingPatientDelayReasonKey] [int] NOT NULL, [AnaesthetistTreatingPatientDelayTime] [int] NOT NULL, [PatientReadyForSurgeonDelayReasonKey] [int] NOT NULL, [PatientReadyForSurgeonDelayTime] [int] NOT NULL, [PatientReadyForTheatreDelayReasonKey] [int] NOT NULL, [PatientReadyForTheatreDelayTime] [int] NOT NULL, [StartOfCaseDelayReasonKey] [int] NOT NULL, [StartOfCaseDelayTime] [int] NOT NULL, [PatientIntoTheatreDelayReasonKey] [int] NOT NULL, [PatientIntoTheatreDelayTime] [int] NOT NULL, [AnaestheticInductionDelayReasonKey] [int] NOT NULL, [AnaestheticInductionDelayTime] [int] NOT NULL, [PatientReadyForProcedureDelayReasonKey] [int] NOT NULL, [PatientReadyForProcedureDelayTime] [int] NOT NULL, [StartOfProcedureDelayReasonKey] [int] NOT NULL, [StartOfProcedureDelayTime] [int] NOT NULL, [KnifeToUterusDelayReasonKey] [int] NOT NULL, [KnifeToUterusDelayTime] [int] NOT NULL, [SuturingBegunDelayReasonKey] [int] NOT NULL, [SuturingBegunDelayTime] [int] NOT NULL, [EndOfProcedureDelayReasonKey] [int] NOT NULL, [EndOfProcedureDelayTime] [int] NOT NULL, [RecoveryRequestedDelayReasonKey] [int] NOT NULL, [RecoveryRequestedDelayTime] [int] NOT NULL, [PatientLeftTheatreDelayReasonKey] [int] NOT NULL, [PatientLeftTheatreDelayTime] [int] NOT NULL, [PatientRecoveryStartedDelayReasonKey] [int] NOT NULL, [PatientRecoveryStartedDelayTime] [int] NOT NULL, [PatientFitToLeaveRecoveryDelayReasonKey] [int] NOT NULL, [PatientFitToLeaveRecoveryDelayTime] [int] NOT NULL, [WardNotifiedDelayReasonKey] [int] NOT NULL, [WardNotifiedDelayTime] [int] NOT NULL, [PatientLeftRecoveryDelayReasonKey] [int] NOT NULL, [PatientLeftRecoveryDelayTime] [int] NOT NULL, [PatientIntoDSCWardDelayReasonKey] [int] NOT NULL, [PatientIntoDSCWardDelayTime] [int] NOT NULL, [PatientLeftDSCWardDelayReasonKey] [int] NOT NULL, [PatientLeftDSCWardDelayTime] [int] NOT NULL, [SYSDateLastUpdated] [datetime] NOT NULL, [SYSDateLoaded] [datetime] NOT NULL, [SYSSourceSystem] [varchar](8) NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Fact_APC_Theatres_Times] ADD CONSTRAINT [DF_Fact_APC_Theatres_Times_CaseSurgeonKey] DEFAULT ((-1)) FOR [CaseSurgeonKey] GO
I also know the connections work as I've tested them and the come back with "Test connection succeeded".
I've checked the "Allow remote connections to this server" is ticked.
Has anyone got any ideas as to why I'm getting this error and what to do about it?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 1

This sounds like a problem that is pretty common. You need to establish a connection before you change the model. I generally process the table before trying to make changes. Once you type in your password and save it, then it will work if it's that problem.

richardmgreen1 SSChampion Points: 10356 More actions · Answer 2

That was just about it.
I tried to process the model after restarting Visual Studio but it still didn't work.
Looks like it was either forgotten password or the saved one had got scrambled somewhere.

Retyped both passwords (we have a domain account and a SQL only login) and all is now back to normal.