The limits of my language means the limits of my world --Ludwig Wittgenstein
A properly designed star schema, along with appropriate performance optimizations, provides good performance and limitless scalability for the largest of the data warehouses. The main criteria for a properly designed star schema is that the dimension row counts are much smaller relative to the fact row counts. But there are dimensions, such as the customer dimension, that can be very large. These are called the monster dimensions, and stretch the star schema to the breaking point. These dimensions must be handled with caution, the bothersome data should be taken outside the data warehouse where appropriate.
The customer dimension can easily exceed 100 million rows for a large business. The situation is worse when the customer attributes need to be tracked at the account level, and the customer dimension morphs into the customer-account dimension. Further the changes to the dimension may need to be tracked over time, resulting in type-2 rows being added at regular intervals.
There are several techniques to deal with monster dimensions. You start by re-arranging the dimension attributes into several mini dimensions. Thus you get the demographics dimension, the customer-account status dimension, etc., in addition to the core customer-account dimension that has the basic customer information such as the name, address, etc. Then you convince the users that change tracking is not needed on the core customer-account dimension for the trend reporting and the longer term decision making. Thus the type-2 change rows are eliminated altogether, the changed type-1 dimension attributes are simply overwritten and historical changes are lost. Even without the type-2 rows, the core customer-account dimension is still quite big. You might be able to convince the users that the core customer-account attributes are not needed at all, and eliminate it altogether. If this approach satisfies the needs of your business, congratulations! But, the users may need access to and may want to track the changes to the core customer-account attributes as well.
At this point you need to clearly identify, why this information is needed and delineate the trend reporting requirements from the operational as well as statistical data-mining requirements. If the requirement is to track the customer-account changes to respond to the customer inquiries, it is really an operational requirement and, an operational application using normalized data structures should be built to support it. If the requirement is to track address changes for fraud detection, it is a data mining requirement and, a flat data structure should be built to support it. Either way, stuffing this data forcibly into a star schema will not yield satisfactory results.
