Fact-and-Dimension vs Storing Character Values

A little difficult to know what target we're aiming for here, as you talk about DW design best practice, but then talk about a report for which we have zero detail on what it's going to contain.   Data warehouses are often "de-normalized" to avoid adding things that might be able to be categorized as "unnecessary complicating dimensions."   There's no value in a 27 million row dimension that doesn't provide some kind of statistically significant value, and adding the phase name to the sales fact table that has the invoice data in it sounds like a reasonable idea as long as there's no longer-term value in having the phase be a dimension unto itself.   That's not to say you couldn't do both, but only use the phase dimension when you really need it.   As with so many things SQL Server, the answer is almost always, "it depends".

Sorry, I was going for brevity and probably needed to add more context.

Ultimately, the report will need to display the following:

My main concern is table size on the disk, and how quickly that will grow throughout the year. The fact table will be 27 million rows, the dimension is only 98 rows.

Would it be better to write a view to join the fact and dimension to display the required output? It would help keep data size low, but may cause performance lag when loading the report.

I'm having trouble with needing a data warehouse to create such a simple report.   That kind of report just doesn't need a DW to exist.  In fact if you parameterize the query to allow specifying a particular invoice, you've taken a need for the DW entirely out of the picture.   If load on the production system for reports is too high, you can use replication to copy the live database to a reporting server.   If I've missed your point, please educate me, because I can't find a reason to DW this scenario in the first place, whereas I can find tons of good reasons to trace an invoice through all it's phases.

Thanks so much for your responses, and sorry if I haven't provided enough context.

A data warehouse already exists for reporting throughout the sales department. We've got 8 or 9 fact tables and about 12 dimension tables. This specific routing report is ancillary to the main goals of the organization.

My question really drives at wanting to keep data size to a minimum so as not to make this report a bottleneck, but also keep it responsive and limit processing time to the cube. I worry if I store the fact table for this Invoice Routing with 27 million rows of Invoice Number and PhaseName (int and varchar(255), it'll increase overall query time when the cube is processed.

And a couple of other thoughts...   I'm doubtful that even if you kept phase as a standalone dimension, I don't see much value in it.   About the only way that has value on it's own is if the existing phase data suggests a fairly strong pattern that is considerably stronger than merely being statistically significant.   The time frame of a DW is typically to span 5 to 10 years or more, and phase data is too likely to change pattern in that time frame in an entirely unpredictable way.   I'm struggling with the idea that phase data is relevant in anything but in aggregate for a measure of what phase something is in after a fixed amount of time from invoice creation.  For example, on average, what percentage of invoices are in each stage a given number of months from creation.   As that can be reported without using a cube, I'm struggling with why it's needed in the DW, unless the DW is the only reporting source.   I'd keep it out of any kind of dimension, because I'm not convinced that there's enough value to spend any resources on it at all.  Once you explain how you're getting all those 27M rows in the DW, I might have a different perspective....

Good questions. The 27M rows comes from the following:

• 600,000 unique Invoices
• Average 45 stages per Invoice. Some will have significantly more, some will have significantly less. For example, some Invoices may only hit 12 Phases, some will hit all 98 (not common but it happens.)

A couple of things here:
Not sure why you would be dissuaded from putting the data in a data warehouse.  You could create the report from the operational system, but it would have an effect on that system.

The basic process you're describing is an accumulating snapshot report.  This would normally have a single row per invoice with the current phase in the record.  That row is updated as the row moves through the phases.  It's not uncommon that some or all of the phases might have a separate column for the date. 

You have a lot of possible phases, so this may not be possible.  Given the layout that you proposed, having a fact table at the granularity of invoice/status change may be necessary.  As you point out, it will have a lot of rows.  You may want to add a metric for time in that status and have some indicator if the status is the current one.  That doesn't exclude a fact at the granularity of invoice with only the current phase and columns with the key dates.  The date dimensions can be set up as role playing dimensions.  This kind of fact table also has to be able to deal with update, unlike the more common transactional ones (like the invoice/status change granularity fact table would be).

One more thing:
If the report intention is to report on individual invoices, as your example indicates, than that might be better done through an OLTP system as another poster said earlier.  Data warehouses are better at aggregating large volumes of information to detect patterns (how long on average is an invoice at a particular stage) and identify outliers (this invoice has been open for a year--which is it and what's wrong?).  If you need to identify the history of an individual invoice, that is better suited to OLTP reporting.  With apologies to the previous poster who said something similar.