Healthcare billing fact grain

I am trying to create a data mart that contains healthcare billing information. The source data I'm loading from contains a "billing master" which is created from multiple claims. (Selective information is gathered from each claim to create a single billing master record). The billing master can have many repricing attempts. Each repricing attempt can have many different pricing rules. The business users want to be able to run adhoc queries on a single star (meaning one fact table) on any combination of the above. They use a BI tool which requires a single fact to contain all of the fields needed for querying. The grain (if I don't consider the claim information) would be at the pricing rule level. However, the problem I'm having is they also want the claim information. The claim information they want to use comes from several different tables and is not related. In the source model, they only capture what is needed from the claim, not everything is captured. How can I give them disparate claim information in a single fact? Should I make some of the claim tables dimensions instead? I've tried to upload a high level diagram of the source model, but I'm not sure it worked.

I'm in Health Care Insurance. I have a summary table that has claims, members, claim dollars, premium, admin fee, etc. It's populated from my Claim Fact, member fact, and Billing Fact. I revise the data for 11 months to account for retroactive changes in membership and corrections/changes in the billing data.

The summary table is at the Account, product, Coverage Type, Billing, and Paid/Enrollment/Billing Year month. The Billing dimension is applied to the Claim based on the Date of Service. This allows the users to obtain Loss Ratio, total claims paid, total premium, total Admin fee, total member by Product, funding arrangement, Paid Date, Coverage Type, account, and rate. It's a tricky summary table to build, but extremely useful.

If they haven't already asked, the users will want to be able to look at Claims and premium on a per member per month basis.

Thanks for the quick response. They do not want me to summarize anything yet. They are not sure how they will use it. It's purely adhoc. There are a lot of users and they want them all to be able to slice and dice the data however they like. I tried to get them to tell me some things they are hoping to get from the data, thinking it would give me some perspective on how they will use the data. It didn't. The claim data in this case is more supporting information. There is a claim payer table, claim diagnosis table, claim procedure table. None of these claim tables have a relationship between them. They don't even have a claim id. Each one is a one to many from bill master. It's just supporting information to the bill master. There are a few additive facts at that level (like total amount on the claim), but that's it. Most of the additive fields are on the bill master. They are more interested in the bill master data, but want to be able to query on the detailed claim information too. Their BI tool can only see on complete star (1 fact) at a time. If I create a fact at the claim level (not even sure I can) and another at the bill master level, they can't use both together. I know this is a little strange. I wish I could figure out how to put the data model diagram in here. I think it would help.

Maybe it's easier just ask generally (not confusing things with healthcare).

If I have two transaction tables, table 1 is a one to many with table 2. I would create the fact grain at the lowest level (table 2) and duplicate the data from table 1 for every row of table 2.

What do I do if I have multiple transaction tables. Table 1 is one to many with table 2, table 1 is one to many with table 3 and table 1 is one to many with table 4. Table 2, 3 and 4 are not related. How do I get the lowest grain of data?

I considered making the some of the child tables as dimensions using a bridge table. However there is additive data on the child tables.

I need one fact table so the BI tool and use any of the fields in single query.

They want claims and billing data in 1 table without summarization?

How can I give them disparate claim information in a single fact?

Does the billing information contain the claim # that it refers to? If not, has the business identified rules that one could use to associate the two?

From a data warehousing perspective, the best way to do this would be to implement claims as a subject area, then create an aggregate fact of billing and claims data, or rely on a BI layer to provide the same. The aggregate would combine dimensions and measures from each.

Right now there's not a claim id between the claim related tables. I might be able to ask for one to be added. They are not really looking at the claim data like you would normally expect. This data mart is more center around the bill master. The claim data is more as supplemental information that was used to create the bill master record. They only store a few fields on each claim table. For example, the claim diagnosis table only has the diagnosis code, type and a present on admission flag. The claim procedure table has the code, type, modifiers and charges. The claim payer table has a few more fields (insured demographics, provider, estimated amount due, prior payments). The bill master in contrast contains over 200 fields, including the fields from the orginal claim that are store in the claim tables. The majority of the queries will be focused on the bill master. A select few will also want to include the claim specific data.

flabbott wrote:...The business users want to be able to run adhoc queries on a single star (meaning one fact table) on any combination of the above. They use a BI tool which requires a single fact to contain all of the fields needed for querying...

Don't you just love requirements like this? I mean, I don't want to tell you how to do your job, but develop a solution to support all my needs and make sure it fits nicely in one table. All because some genious decided to purchase a "BI" tool that has less functionality than Excel. It sounds like your first goal is to declare the grain of your fact table. If you can't get your metrics to line up nicely due to cardinality differences you may need to build multiple facts and present a view to the "BI" tool or only present aggregate level metrics to the users.

It's definitely a challenge! Unfortunately I don't know the BI tool that well (Jasper), so I can't really speak to what it can do and not do. That's the frustrating part. How would a view work? Create a claim fact and a bill fact and join them through a view?

For the diagnosis, I was looking at using a bridge table. The only attribute I would have to deal with is the present on addmission flag. Are there any issues to adding that field to bridge table? The bridge table would look like this:

diagnosis group key
diagnosis key
present on addmission flag

Would there be any issues with this?

Assuming I can get a claim id, I still want to bring the grain of the fact table down to the claim level because most of the queries will be at the bill master level. It seems like I will be hurting the majority of the queries for the sake of a few rare others.