This is how I would approach it myself. Remember to think of your Facts as "actions" or "verbs" and your dimensions as the the descriptors of your facts.
So your invoice and invoice line items are both facts. One approach to dealing with this is what you mentioned, making the fact table to the granularity of the invoice level line item. This will lead to redundancy of certain things like you mentioned. This if fine for things as an billing address dimension and certain invoice level facts such as a tax could even be broken down to the line level item and it could then aggregate back up to the invoice level amount.
This approach can break down if you have invoice level facts that don't break down nicely. For example, are customers allowed to have more than one payment method? Could there be more than one type of marketing material attached to the invoice? Does the marketing material correlate to specific line items?
Asking these questions can definitely lead you to creating multiple facts such as FactInvoiceLineItem, FactInvoice, FactPayments, etc. that you end up tying together with something like invoice number. Doing this will give you more flexibility to analyze specific facts better.
If doing this approach and you are building cubes off the fact tables, I would recommend using views to load your cubes. This way you could have a high level view for the invoice that joined your various fact tables and had measures on there such as number of line items, number of payments, etc.
All that to say, I believe going that route is much better than splitting out your fact tables that have the same granularity.
Feel free to respond with more details if you need more help hashing it out.
You can achieve this by creating 3 or 4 tables:
- Company Information
- Fundamental Entries
- Fundamental Data
- MarketCap Data (Optional)
Company information will contain information about all the companies, assign a Company ID to each company, which would be later used in to join the financial fields. Use some SCD type( slowly changing dimensions) based on your requirements, fields like Ticker changes often. So carefully assign a company ID.
Unpivot and divide your financial data between Entries and values.
In the Fundamental Entries table generate a unique ID (eg ID_FS_ENT) for each company ID and each financial entry (that would be a combination of Period End, Accounting Standard, Fiscal Period, etc.)
In the financial Data table store the financial value along with the ID_FS_ENT, Field Name (that would be shares outstanding, total fixed assets, earnings etc)
If you are also receiving daily market cap for each company then store it separately. Again use the Company ID, add trading date and value to this table.
Hope this helps!
P.S. I am already managing a similar database with more than 100,000 companies for more that 78 economies and around 100 million financial entries.
Best Answer
First in this case I suggest you to adopt a star schema. the transaction_data table obviously will be you reference to construct your fact table because it contains 'measures' like quantity,sales_value and other numeric attributes , the fact table also will contain substitution-keys of dimensions tables like Time, Product, Customer,demographic, etc.