posted 2/12/2012 by SQLKohai - Views: [954]
I'd like to start posting more code that I have in hopes that people with more experience than I have will steer me in the right direction, and also to pass on some code and ideas to someone who might be just getting started.
I have some experience now with SSIS and SSAS, enough that we can maintain and enhance our cubes and reports to meet our users needs. What I'd like to do is start from scratch and build one that is cleaner, and take out a lot of the fluff that we don't need. We had a much bigger plan in the beginning and a lot of the stuff fell by the wayside, mostly because we met most of our needs simply. I believe we totally over engineered it. By starting over (in my own time), I can really see why we're doing things instead of just taking what the consultant gave us when they left abruptly.
That being said, I'd like to start small.
I have been putting together a new cube, but I'm running into some roadblocks that I'm unsure of, so I wanted to air it here to see if anyone else was having similar issues. I start with a simple aging fact table.
CREATE TABLE [dbo].[FactARAging]( [PK_FactARAgingKey] [int] IDENTITY(1,1) NOT NULL, [FK_entityKey] [int] NOT NULL, [fk_ResidentKey] [int] NULL, [FK_periodDateKey] [int] NOT NULL, [ARAccount] [int] NOT NULL, [Payor] [nvarchar](255) NOT NULL, [ARCurrent] [decimal](18, 2) NOT NULL, [AR30] [decimal](18, 2) NOT NULL, [AR60] [decimal](18, 2) NOT NULL, [AR90] [decimal](18, 2) NOT NULL, [AR120] [decimal](18, 2) NOT NULL, [AROver150] [decimal](18, 2) NOT NULL, [ARTotal] [decimal](18, 2) NOT NULL, [RowCreated] [datetime] NOT NULL, [RowUpdated] [datetime] NOT NULL, [FK_auditKey] [int] NOT NULL ) ON [PRIMARY]
It's being pulled directly from an extract using an SSIS job (remind me to post it, it's good stuff), directly from our third party billing application. Just ages Accounts Receiveable by Resident and Entity(or building).
My DIMS so far are just a DimDate(fk_periodDateKey) and a DimEntity(fk_entityKey). That part is fine. It's when I add in a third dimension that things get whacky. What I really need when I look at the Aging data is to know WHO the aging belongs to.
Here's my first major question. I want to add a new Dimension, DimResident. Am I just hooking it to FactARAging based on the fk_residentKey, or a pair of keys, resident and fk_entity?
2nd question - can I have a heirarchy under resident? I wouldn't think so, mostly because I already have one set up for Entity. (Company, Region, Entity) I tried adding it, and it dropped in the Dimension with the measure group attached to both keys. Is that right?
I think my biggest concern is not snowflaking, which would lead me to believe that I shouldn't have a binding based on the pair, just go with the resident key.
just in case, here's the DimEntity:
CREATE TABLE [dbo].[DimEntity]( [PK_EntityKey] [int] IDENTITY(1,1) NOT NULL, [EntityNumericId] [int] NOT NULL, [EntityId] [varchar](10) NOT NULL, [EntityShortName] [varchar](50) NULL, [EntityLongName] [varchar](50) NULL, [EntityGroupName] [varchar](20) NULL, [EntityGroupShortName] [char](3) NULL, [EntityGroupId] [smallint] NULL, [OrganizationId] [int] NULL, [OrganizationName] [varchar](100) NULL, [RegionId] [int] NULL, [RegionName] [varchar](100) NULL, [PortfolioId] [int] NULL, [PortfolioName] [varchar](100) NULL, [BusinessTypeId] [int] NULL, [BusinessTypeName] [varchar](100) NULL, [AdpId] [varchar](50) NULL, [eHealthDataId] [varchar](50) NULL, [PowerProId] [int] NULL, [RegionReportingId] [int] NULL, [EntityEmail] [varchar](100) NULL, [DBSequence] [int] NULL, [DBConnection] [varchar](750) NULL, [LicensedBeds] [int] NULL, [AvailableBeds] [int] NULL, [LastArCloseDate] [datetime] NULL, [ActiveSwitch] [int] NOT NULL, [PCCStartDate] [datetime] NULL, [PCCFacilityID] [int] NULL, [RowCreated] [datetime] NOT NULL, [RowUpdated] [datetime] NOT NULL, [FK_AuditKey] [int] NULL,