Post reply

Distinct count for Data Mart process

  • June 9, 2010 at 9:29 am

    #387109

    I have a process in SQL 2005 that runs every night. This process populates my dimensional database (4 fact tables and 9 dimensional tables). Also, this process does all the aggregates that I will need for reporting. I decided to do it on SQL because I’m faster coding this way that in SSIS (for now).

    I found a “bug” in my process where I’m not sure how to fix it. One of my Calculations during the process is to get the distinct count of a product during the entire period of time where the product was present. Other words, if my HD Box was present (or running) during the period between April 1st to April 30th I should have a distinct count of 1 time, even if the HD Box run every day. But, the dimensional database has a dimensional date.

    My fact Table will have a total of distinct count by day. If my HD Box runs every day it will count a distinct by day. When I run the report to get the sum of the distinct count by the period of time given; I will get count duplicates because my HD Box was count by day.

    Is there anyone can advice me about this kind of matter?

    Thank you in advance

  • June 11, 2010 at 8:44 am

    #1179233

    Not sure if I got exactly what the issue is but it looks like the process intends to tell if some product "run" or not in a specific period of time... like 1=It did; 0=It didn't.

    If business specs are asking to have that determination made by both Month and Day I would do two "counts", one grouping by YYYYMM and the other by YYYYMMDD.

    As I said, not sure if I got it right.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply