Cannot insert the value NULL into column 'SOURCE'

[Former Member]

System:

SAP BPC 5.1 SP1

Multi server - 1sql/1web

History:

We recently have been receiving several errors after we added an H3 to our Location (entity) dimension. The first issue we stumbled upon was the system was allowing users to send to parent members in the location dimension. This created several issues when we tried to optimize or process. Users were also getting the error, "Invalid use of Null;SaveMemberlistInfo" when sending data.

We usually schedule a dtsx package built w/SSIS to process all dimensions optimize overnight. When we ran into the above issues I cleared the data sent to the parent members and processed all dimensions thru Admin. This alleviated both previous problems but I ran into a new issue. When I tried to perform a full optimize, the log file grew to over 40GB, ran out of space and the process failed. We were able to run an incremental optimize with compress successfully. We were eventually able to run a full optimize by restricting the log file to 1GB. Also, during this series of steps we removed the H3 as we determined that may be the root cause.

I thought we were on the up and up until this weekend. Users again began to receive the “Invalid use of Null” error when sending data. I did a full process of all dimensions and once again it corrected the issue.

I am in the process of doing some testing in dev and when I run the SSIS package I receive an error, “Cannot insert the value NULL into column 'SOURCE'…” This is after it successfully rebuilds all of the dimensions. It occurs during the tasks that compress the Fac2, FactWB and Fact tables. More specifically, it occurs after all records have been combined in Fac2 and are being moved back to Fact.

My thoughts are that during these issues a null somehow found its way into one of the tables. That’s the easy part. All tables are set up to not allow null values. I have also queried the Fact, Fac2 and FactWB tables for null values without success. Is there another table that could cause this? Is our DB corrupt?

Any thoughts/insight is greatly appreciated.

Thank you.

Edited by: Anton Chris on Mar 24, 2008 10:17 PM

Edited by: Anton Chris on Mar 24, 2008 10:20 PM

[JHattendorf · Accepted solution]

I've run into the similar problems on a couple of occassions. In each instances, there was something in either the DM package or logic package that caused the problem. Since the issue comes up with both packages and user input schedules, the first place I suggest looking is in the default logic and any logic packages it references.

In one instance, a user deleted a dimension member that was being used by a logic script and led to a similar problem. In another instance, a "small edit" to a logic script caused the script to book values to a parent value.

Hope that helps,

Jeff

[Former Member · Accepted solution]

The SOURCE column is a system column that is used to tag records that are created by a lite optimize which moves data from the writeback table to the FAC2 table. When a full optimization is done, these get moved to FACT table. At the end, all records must have a value of zero in the SOURCE column. Nulls are not allowed based on a restriction on the SQL tables. Is the SSIS package your running doing an optimization and compression?

Since you have a few different things going on, I'd suggest the following:

1. Go into SQL management studio and run a query on FACTWB, FAC2, and FACT with SELECT * FROM {table name} WHERE SOURCE <> 0. If you get any records delete them using the same criteria

2. Go into the AS management studio and run a full processing of the application you're having the issues. Sometimes AS will surface and error message that BPC doesn't specifcally trap in the admin interface. If you get any errors resolve whatever the problem is. Also, process the dimension you were referring to with the H3 in it.

3. Once you get a clean processing in AS and any bad records cleared, the process the dimension and application in BPC.

4. Validate and Save all logic files in the application.

5. Run an incremental optimization (this is a "full" optimization for data.). A FULL optimization does data and structure which isn't necessary.

6. If that works, run the incremental again with compression.

[Former Member · Accepted solution]

Thanks for your response!

The SSIS is performing an optimize and compression.

The SSIS successfully combines (optimizes) the tables. The step that seems to be failing in SSIS is the compression as defined below:

SELECT SUM(SIGNEDDATA) AS SIGNEDDATA,

CATEGORY,

DATASRC,

TIMEID,

ACCOUNT,

DEPARTMENT,

LOCATION,

RPTCURRENCY,

INTCO

FROM tblFac2Finance

GROUP BY CATEGORY,

DATASRC,

TIMEID,

ACCOUNT,

DEPARTMENT,

LOCATION,

RPTCURRENCY,

INTCO

It is then loaded into Factfinance using an OLE DB connection mgr. This works for all other tables except finance. The Error I receive is listed at the bottom. I believe the underlined portion is the issue.

1. The query for source <> 0 returns 0 records. This is also true for any columns = null.

2. We are able to successfully process the application in AS. The H3 no longer exists. I deleted it when we started having issues. All dimensions have been reprocessed successfully with the exception of a “Thread was being aborted” error. I do not get this error when processing one dimension at a time. I have worked with support a couple times on this issue and I was told it is a known error without a resolution. All appears to be correctly processed even when we receive this error. This has been an issue since we installed 5.1 sp1. We installed 5.1 on new servers and did not use the upgrade tool.

3. I am able to successfully complete full process in AS.

4. All logic validated and saved. I did receive an error while validating ICElim logic. It was the out of the box logic and we do not use IC Elims so I commented it out. I was then able to validate and save.

5. I am able to successfully run an incremental with compress. We have 5 applications and I am able to run a full optimize/w compress on all apps except one. The other 4 apps are much smaller than the one that has the error. The one with the error has about 19M rows and the next largest is about 70k rows. I am also receiving a new error when I do a full optimize in Finance – “Thread was being aborted”. This is the same error I get when completing a full process of all dimensions.

I realize we do not need to perform a full optimize and that the same benefits are achieved from an incremental/w compress and processing dimensions (or processing cubes using AS). I am baffled that I can process cubes in AS and process all dimensions without an error yet have the problem with full opt and SSIS. What would be the difference between processing the cubes during optimize, processing dimensions or processing in AS? Why does it work two ways but not the other? Furthermore, I am not sure why I am receiving the error in SSIS as listed below? I’m not sure how but I still contend something became corrupt when we added the H3 and users were allowed to send data to parent members.

SSIS error:

DTS_E_OLEDBERROR.

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "The statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client"

Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'SOURCE', table 'GKempower.dbo.tblFactFinance';

column does not allow nulls. INSERT fails.".

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.

The ProcessInput method on component "tblFactFinance" (58) failed with error code 0xC0202009.

The identified component returned an error from the ProcessInput method.

The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED.

Thread "WorkThread0" has exited with error code 0xC0202009.

There may be error messages posted before this with more information on why the thread has exited.

I apologize for the long post… Just trying to give as much info as possible. As always, your insight is appreciated.

[Former Member · Accepted solution]

The query you list, assuming all fields in the table except source are listed, is correct. The compression simply groups on all column and sums signeddata. This goes into a temp table. The index on the FACT table is then dropped to speed up inserts. Then the FACT table is truncated and the summarized records are written back into FACT from the temp table. Finally the index is added back. But, the insert statement should be writing the value zero back into the SOURCE column on all records. The 19M shouldn't be an issue. I've compress applications wiht 90M records in the fact table without any issue also on 5.1 SP1. It takes a while but it works.

From what you've described, it does sound like everything at an AS layer is working. The only difference in processing in AS vs BPC is the extra steps BPC does that are application specific such as setting the applciation status to off-line, moving data between the fact tables etc. At the end of the process, it's still sending a command to AS to process the cube just as you did.

Some of the error message you're describing almost sound like processes timing out.

Is the account dimension that you had the H3 in shared in any other applications? If so, are those application working correctly? If you don't have it in another application, it might be worth creating another application by copying finance application. Copy everything including data then do a full optimize with compression and see if that works. If it does you could delete the original application and then use the copy you made to create the the original applciation with the same name again. The only thing you may have to redo is security which might or might not be a big deal depending on the number of users and how well you have the user security documented. You'd obviously need to make sure you have enough space on the database and file servers to hold two copies of the application.

[Former Member · Accepted solution]

Chris,

The items you mention may indicate an issue only with the 3rd Hierarchy. You cannot build a hierarchy and post / send / load data to a parent member, even if that member exists in another hierarchy, as you mention in question 5 last sentance. It may be as simple as you have values in the table that refrence the Parnet member, which would cause the process to fail via admin console, My advice is the following; Comment out the 3rd hierarchy by changing the PARENTH3, to something like P3. Try to process the dimension using the admin console. Try to optimize and compress just the 2 hierarchies to get back to a stable state. If you still have errors, then scan the records for any H3 members that you may have inserted values in the fact tables and remove the values, reprocess via admin console. Then you will need to add a third hierarchy that may leverage the existing Parents, but has its own children for data entry and capture.

[Former Member · Accepted solution]

1-5 was in response to James' post earlier in the thread. All of the steps you have suggested have been previously completed.

Thanks.

[Former Member · Accepted solution]

Hi Anton,

I am also getting the same error(Cannot insert the value NULL into column 'SOURCE') while running the SSIS packages for one of our application which has 74 million records and we are on BPC 5.1. I also tried following the instrictions mentioned in the note 103040. It worked once but then it again started giving issues.

It will be great if you know the solution for this problem. We do not have anything in the logic and i still saved and validated them. I have tried the incremental optimization and it goes fine but the incremental with the compress option fails with the "Abort error" as mentioned by you.

Thanks and will appreciate your response on this.

Regards

Raman

[Former Member · Accepted solution]

Hi,

Just wanted to let youknow our issue was resolved and it was becasue in the column properties for Source the Default and binding value should be zero and it was not there. once this was added then load went fine. THanks

Regards

Raman