Recently I had to create a SQL Server user which was going to be used by OBIEE to writeback to the reporting warehouse. Dangerous you may think. It is dangerous because you're under the risk of altering/deleting information from the warehouse forever - with no chances of recovery.
SQL Server is good in setting permissions with various granularity levels. I created a database role of writeback on the database (not server level) and gave it absolutely no permissions to start with. I then added a Securable (Right click -> Properties -> Securables) by clicking on Add and searching for the staging table in which writebacks'd be written to. Set the required permissions.
Now create a database user say writeback_user and map it to the required database with the role of writeback.
I'd recommend writebacks to be written to separate staging table and then consolidated later with the data warehouse tables. This way you can do checks on the data if you may before writing to the warehouse tables.
No comments:
Post a Comment