SQL Server Performance Forum – Threads Archive
Accessing Fact & Dimension Tables Outside of DW
I received a request from a development area to allow their non-warehouse application to access Fact & Dimension tables. Is this adviseable? My gut feeling is this should not be allowed, but I’m not sure what problems this may cause. Please advise. Thanks, DaveThere is a lot information in Microsoft about DW. One of this:
http://www.microsoft.com/sql/evaluation/compare/analysisserviceswp.asp Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Dave Is there any reason why they wanted access to OLAP databases?
I would go with your feeling to not allow non-related applications. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Satya, There are several reports circulating that provide people with Insurance Agent information. One department noticed the data that existed on our Intranet was not up-to-date and wanted it to be in-sync with the data warehouse. I convinced everyone the data they needed can be found in the "staging tables" used to populate the warehouse. While I’m still not a big fan of letting people access these tables it’s better then letting them access the Fact and Dimension tables. Unfortunately I still am unclear about the problems it could cause, outside of locking. I’ll check out Luis’ link. Dave
Dave Thanks for the explanation, I was in the same position earlier and straightaway rejected in terms of performance issues. If its inevitable you can arrange for a DTS job or else restrict them with strict permissions on the dw database, simple rule. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Glad to hear I’m not the only one getting these type of requests. Thanks for the advice, Dave
BTW do you have any proposal in mind to cope-up this permission request, just like to know and share. Thanks. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
No proposal yet. I still need to fully comprehend the risks before I can educate our development area and business units on the pros and cons. Dave
The problem with DW is a limited help and resources from MS too, all you need to do is trail and error process. And also I’m going thru available material to understand in and out of DW Performance. I will update once I’m confident with my approach. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks Satya. I will also post an update if I find anything of use. Dave
Looking up agent information is a transactional process, not analytic, so I’m not sure why the developers would want to use the DW instead of the live OLTP system to lookup agent information, unless they want to perform some analysis that takes into account historical information not captured in the transactional system, and in this case, they better be using your UI browsing tools. It seems to me that your setup is a plain-vanilla SQL data warehouse without OLAP/AS cubes. Otherwise, once you reporcess the cubes from the DW, the reports generated on your intranet should be in sync with the data in the DW because they’re generated dynamically. If you’re generating the reports statically off of the DW DB on a periodic basis, then it’s possible that there’s a gap between the time the DW gets refreshed and the batched reports get generated. This gap should be small (depending on your ETL window), and I wouldn’t allow other developers to touch the DW tables to get data that is probably a day or less fresher. In addition, the staging database should be a black box to the outside world because its main purpose is the ETL process and its meta data. These will change very often as you keep the data warehouse and the live OLTP system in sync, so you don’t want other processes that has nothing to do with ETL to have dependencies on it. Worst case, create restricted views for other developers and grant read-only permissions.
Homam, Have you implemented this sort of setup at your end? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Homan, The agent information they seek is used for both transactional processing and analaytic, since some of the information is generated in the form of reports triggered from an intranet application. The data warehouse in question does consist of cubes, which are populated on a monthly basis to show prior month data. Unfortunately reprocessing a cube does not keep the intranet data in sync since the intranet is not using the same data source as the warehouse. The information the users need exists on two different systems. One system is an HP3000 mid-range running HP-Image as the database. The other system is Intel-based and uses SQL Server 2000 to store the data. The data warehouse staging environment is populated daily by a process that creates flat files of data from the HP3000 and the Intel-based servers. A VB script reads the flat files, performs validation and formatting and then populates the staging tables. Because of data existing on the HP3000, the intranet application cannot easily retrieve the data directly. For this reason they were inquiring about using the data that exists in the data warehouse. The potential for the staging tables to change is a good point that I’ll keep in mind for future requests of a similar nature. The dependency issue could present future problems. Thanks, Dave
satya: My setup is a standard data warhouse/OLAP solution. We have many live OLTP databases and servers from which we extract data on a daily basis into a separate staging database that takes care of the ETL (consolidating, cleansing, scrubbing, resolution, matching, mata data management, and converting to a dimensional model). The ETL then populates another database, which is the data warehouse and it contains multiple data marts. From these data marts we build OLAP cubes yet on a separate box. All UI and client access takes place only from the OLAP cubes, be it a web portal interface using custom ADOMD/MDX, sophisticated clients using Excel or OWC ActiveX controls, or packaging cubes in local files to be FTP’d to our third-party partners for use with their own analytic platforms.
DBADave: I’m pretty sure there are justifications for the setup you’re using, but I would look for a way to centralize all the data used for the reporting environment. This is what the data warehouse is for. You don’t want your clients and developers to generate analytic reports from separate sets of data because these reports will not only be out of sync, they will also be inconsistent and not be able to give you the whole picture.
Homam, thanks for the tip-off information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>