SQL Server Performance

Accessing Fact & Dimension Tables Outside of DW

Discussion in 'Analysis Services/Data Warehousing' started by DBADave, Oct 13, 2003.

  1. DBADave New Member

    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, Dave
  2. Luis Martin Moderator

  3. satya Moderator

    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
  4. DBADave New Member

    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
  5. satya Moderator

    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
  6. DBADave New Member

    Glad to hear I'm not the only one getting these type of requests.

    Thanks for the advice, Dave
  7. satya Moderator

    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
  8. DBADave New Member

    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
  9. satya Moderator

    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
  10. DBADave New Member

    Thanks Satya. I will also post an update if I find anything of use.

    Dave
  11. homam New Member

    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.
  12. satya Moderator

    Homam,

    Have you implemented this sort of setup at your end?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. DBADave New Member

    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
  14. homam New Member

    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.
  15. homam New Member

    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.
  16. satya Moderator

    Homam, thanks for the tip-off information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page