Of course what i am going to describe is not reproducible so reporting this won't have an effect but I want to describe what I saw (pretty sure of that - things were bit under pressure since it had an effect on production runs) - perhaps some of you have experienced the same.
Production is run where users change some numbers during the day and at night board database is generating reports which serve as input for another system which in turn churns out new numbers which in turn as placed back in board. Those nightly batches are called with the procedure launcher. Credentials of a (admin) user are used to run these batches. Pretty standard stuff.
One morning customer notices that the normal file they get (for their systems) dropped in size. Weird - it can fluctuate on a daily basis but shrinking 80% was not normal. Investigating all the steps involved from input, process, to output did not reveal any mishaps - it ran fine the other days - actually it ran fine for weeks now - what could be wrong?
At that same time I get reports that some users could not get access to another database on that same engine. I investigated and saw what the user was seeing. Some dataview on the landing page was not populating data. From when I was developing this page I know that it had to do something with the security. I noticed in the selection screen i had some selections that were needed but i decided to remove them see if it would have any effect. It did not. Now i was without selections - hm - except one on users. We set this in the database profile with "select <entity>=@user" but the developer account I use is not using this profile. What ever i tried I could not reset the selection - it always stayed: Total 70 / Selected 1. I also noticed the user id. Glad that I discovered something I decided to move back to production where numbers were disappearing - it was a more pressing situation.
In trying to address the issue in production we discovered another (not related but needed to be fixed) issue where we switched form reading all data to only the daily delta and I noticed we were loosing all existing data from the time range we were reading in the delta reader - not good - we had to switch to reading into a temp cube and then merge the data with a dataflow. Simple. But in testing this I found that no matter what flow I used the receiving cube would be wiped clean - a suggestion to reboot the engine did the trick.
In addressing the loosing numbers issue I figured out that only two values for area were reported on - the same areas that the user id mentioned before had access to - wait a minute - I connect with admin access and launch a database procedure and board applied a database security setting which limited the view of the data to those two areas - wow! I wonder how that could have happened - weird very weird - of course the reboot fixed this so I spend all day trying to figure what could be wrong by running all batch steps one by one and meticulously checking results.. - nothing could be found though.
Sorry for the long read but I wanted to share my experience... Good day to you all!
