Count zeros

Question

Count zeros

Nicholas Large

SSC Enthusiast

Points: 136
More actions
August 18, 2007 at 11:24 am

#180318

Hi
I am very new to AS, four months actually, and have been working on an example model, really to get me associated with AS. I have a server time dimension, a letter dimension (will explain below), and an agent authorization dimension.
The server time is a standard ST dimension.
The Letter dimension is a (fact-)dimension containing information about what letters are sent to customers on specific dates it is something like:
Agent Number
Date Year
Date Month
Date Day
... (other information not relevant to this question)
The Agent Authorization dimension looks like:
User ID
Security Type
Representative Code (Like a manager ID, but not quite)
Agent Address State
Agent Status
Agent Number
...
The concept is that every user has an NT login. This is the User ID. Each user has many security types associated with their login. Each user can see different representative's via their security, so one security type contains a different list of reps. then another sec. type. would. Each Rep has many agents associated with them. So:
(Agent Authorization Dim)
User --< Security Type --< Representative --< Agent (Letter Dim)
Agent --< Date (one entry with a date = count of 1) and of course many agents exist in one state and each agent has an active or inactive status. These agents send letters to customers. It is important to the companies income that agents send lots of these letters out to people. My task was to build a cube representing this, and then build a report using reporting services which shows (Per User), each representative's agents and the total count over (Server) time of a particular letter, hence: 2004 2005 2006
J F .. D J F .. D J F .. D
Rep A1
Agent A0123 10 | 15 | 29 | ...
Agent A0245 ... ...
Rep A2
Agent A0246 ...
So, my report works, however, if there is no entry on a particular day/month for any agents then the server time dimension skips the month entirely. (NB. Is this something that could be resolved at Reporting Services level in the report, or is this something that I can resolve at AS level?)
I want to make sure that representations of time include those where there are no letters sent so that there are no gaps in the months and days.
I thought about inserting items in the mart for entire calendar periods and then updating with real data but that wont work because I use 'Letter Item Count' measure and if I insert an item then it will count 1 not 0.
Any help would be greatly accepted.
Regards,
Nick.

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Not sure if this can be done in Analysis Services because I don't use it

In the T-SQL side of the house, we do this by joining to a table that has all of the dates. I don't actually maintain a table of dates on most installations, but I do maintain a Tally table. A Tally table is nothing more than a single column of well indexed sequential numbers and you can do some remarkable things with it (ROWNUM OVER PARTITION is the preferred method if you have 2k5... I'm stuck with 2K).

Here's how to make a Tally table...

--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

Ok... how to make dates out of this... let's just say that you pass a full date to a datetime variable and you want all the dates for the month that full date is in... this will do the trick (it's an example that I've recently shown someone else. You will need to modify it to suit your situation)...

--====================================================================================================================
--      Setup the test data.  This is NOT part of the solution
--====================================================================================================================
DECLARE @myTable TABLE
        (
        DataDate DATETIME,
        DataVal  INT
        )

 INSERT @myTable
 SELECT '07/01/2007', 100 UNION
 SELECT '07/08/2007', 120 UNION
 SELECT '07/15/2007', 150 UNION
 SELECT '07/22/2007', 190 UNION
 SELECT '07/29/2007', 210 UNION
 SELECT '08/04/2007', 230 UNION
 SELECT '08/11/2007', 240 UNION
 SELECT '08/15/2007', 245 UNION
 SELECT '08/18/2007', 255 UNION
 SELECT '08/25/2007', 290

--====================================================================================================================
--      Demo the solution using a tally table. I suppose this could be converted to use the date function you spoke of.
--====================================================================================================================

--===== This would be the input parameter of a stored procedure
DECLARE @ReportDate DATETIME
    SET @ReportDate = '20070717'

--===== Declare the local variables
DECLARE @BaseDate   DATETIME
DECLARE @StartDate  DATETIME
DECLARE @EndDate    DATETIME

--===== Determine the range of dates necessary to report the month identified by @ReportDate
 SELECT @BaseDate = '17530101', --Start of SQL dates
        @StartDate = DATEADD(mm,DATEDIFF(mm,@BaseDate,@ReportDate)  ,@BaseDate), --1st of month
        @EndDate   = DATEADD(mm,DATEDIFF(mm,@BaseDate,@ReportDate)+1,@BaseDate)  --1st of following month

--===== Return all dates and values for the month.  Sub zeros for days that have no value.
 SELECT DATEADD(dd,t.N,@StartDate-1) AS DataDate,
        ISNULL(mt.DataVal,0) AS DataVal
   FROM dbo.Tally t
   LEFT OUTER JOIN
        @MyTable mt
     ON mt.DataDate = DATEADD(dd,t.N,@StartDate-1)
  WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 2

It sounds to me that your time dimension isn't being created properly, because you're using the server time build.

When you build a dimension out of the "facts", you may end up missing entries (like this case), since it doesn't have any data from which to build the dimension. This makes sense, since, depending on your data, why would you want to create a dimension entry for which you don't have any data (ever). Part of the job of MSAS (and MDX) is to eliminate these holes (hense, concepts like NonEmpty() ).

Try creating your time dimension directly.