February 11, 2012 at 9:26 pm
I have a little 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.
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]
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?
February 13, 2012 at 7:56 am
Your first major question should be: at what level of granularity do you want to report at? Without us understanding that, it's difficult to comment on your design. If you can clarify that, it should be easier to assist.
Thanks,
February 13, 2012 at 8:00 am
Our dashboard doesn't usually go into that level of detail, it'll usually summarize down to the entity level, and that's sufficient, but the detailed reports that go out on a regular basis need the resident level.
The resident can be in one Entity, check out, and check into another of our Entities at another time. In that case, I believe they would have a new residentKey. It's never happened, so it's all in theory.
February 13, 2012 at 8:15 am
Sorry, but that doesn't answer the question. Granularity refers to what is being measured by a fact table. Does it refer to a single telephone call, a single invoice transaction, the transaction details, a job as it goes through the process from start to completion, and so on. This question must be answered and adhered to. The failure to do this is likely the main cause so many BI projects fail.
Looking at your design, for example, I wasn't sure if the granularity was at the client level and the past due buckets was the summary of multiple invoices or if it was invoice, which would be more normal for this kind of fact table, but which doesn't require all the buckets. Instead you would have the date which would then connect to a date dimension that had these buckets.
I would suggest you get a hold of The Data Warehouse Toolkit by Ralph Kimball and read and understand it.
February 13, 2012 at 8:44 am
I actually have it and was going to begin reading it. Sounds like I definitely should 🙂
In this particular fact table, you're measuring the aging of data, and it's sent to us via extract, so it's at the payer level which is a summation of charges for a patient. We do have access to the individual transactions, but no one would need to go to that level of detail.
So I guess the answer to your question would be, the fact table refers to the amount of revenue we can expect, and how old it is, for each resident, owed to each payer (medicaid, medicare, hospice, etc).
Thanks for your feedback, this is the kind of thing I've been looking for.
February 13, 2012 at 8:57 am
I don't think you're quite there. From what you're saying and what I see in the design, the granularity is the amount due and past due by client by organization due. If I've understood this correctly, you should plan to have a dimension for the organizations due pay. I don't see that key in the proposed design.
I'm glad you have the book. Please dig into a bit, and once you've had a chance to do so and think about what I've written, rework the design and then see if you have questions. It sounds to me, though, as if you are closer to the correct track than you were a bit ago.
February 14, 2012 at 2:21 pm
Hey Ron,
I have his lifecycle book, so I ordered the one you suggested. (I had been meaning to get it anyway)
You had mentioned having a dimension for people that are owed. The only thing we're concerned with on that score is to classify how much is medicaid, how much is medicare, etc, we don't go into individual plans owed, etc.
I believe I've gotten things to work through some trial and error but the strange thing is, I have a dimension for Resident, and there's a large Unknown amount that shouldn't be there. When I look at the table it's coming from in the db, there are no unknown residents (fk_residentkey=0).
When I first created the cube, and brought in the resident table, the cube created two dimensions, one for fk_entitykey from the Entity table, and one for fk_entitykey from the Resident table. I removed the resident fk_entitykey dimension and things seem to be okay. Until I try to use it 🙂
I'm curious to understand where this Unknown member is coming from. I looked and each Entity has an Unknown member and it's not like the numbers are the same across entities. I'm wondering if it's taking all of the aging, matching up the residents that it can, and dumping everything else left into an Unknown resident, then going onto the next.
February 14, 2012 at 6:22 pm
There's a setting on the dimensions that allow the activation of the unknown member. You must be doing something to set it, because it's not automatic. I never use it myself. I always have an UNKNOWN record in every dimension table to handle any unexpected entry. You must do one or the other. I prefer to handle it manually, although I don't know if it's a case of that's the way I learned because you had to do it that way earlier. Using the artificial UNKNOWN, however, I don't how someone would maintain the standard referential integrity.
Glad you got the book. It will take a little work, but once you get how this is done, you will be able to do more than you can imagine now.
February 14, 2012 at 6:31 pm
There's a setting for it, but if I set it, it wasn't on purpose 🙂
I just figured it out. I have a lot of older data that doesn't have a key and I wasn't looking at the current period, I was looking at all older data. I'm feeling pretty dorky right now.
Thanks for all of your help.
February 14, 2012 at 6:33 pm
No need to feel dorky. There are a lot of good DBAs who don't go where you are now.
February 15, 2012 at 7:45 am
Yeah, forget dorky. If we all knew how to do everything perfectly, it wouldn't be near as fun to get that feeling of SUCCESS!!!
I can't be alone in wanting to learn new stuff, and I accept that failure is often the first, and annoyingly persistent, result.
I am thrilled that there are forums like this for when I'm stuck on something that I KNOW has got to be tiny and a stupid mistake on my part.
February 15, 2012 at 7:49 am
Okay, I see your point, and I have to admit, it feels good to be able to know that you solved the problem, even if it was something silly.
To tell the truth, finding something that simple makes me feel better about the whole process in that I don't feel like I am as baffled by the whole thing. We had a consultant come in and set down our data warehouse and cubes and attempted to show us what was going on, but the project kind of flopped and we never really got around to the training aspect.
In the last 2 weeks, I believe I have really gotten a handle on it to the extent that this problem I posed at first is turning into a much cleaner and understandable asset instead of something we were always afraid to touch.
Thanks for your support 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply