Factless Fact and Slowly Changing Dimensions
- pietlinden wrote:
How do I track for changes in Cost/Price? Just add a trigger to Product[UnitPrice] and Purchase[UnitCost] and maybe call a stored procedure from the trigger to (1) find all recipes that use that Ingredient, (2) generate a new version of the Product (increment primary key), (3) generate a new time-stamped version of the recipe to point to the new prices (so new {productkey, ingredientkey, weight} records in the bridge table).
You need an ETL process, that runs once a day and performs the necessary steps on dimension and fact tables. In short, you need a Data Warehouse if you don't have one already. Power BI in itself is not a replacement for that.
I would not recommend a trigger on the source table (if it's a table in a database), but a complete ETL process which extracts the data, stages it and then performs the necessary steps to populate and/or update dimension and fact tables. In other words, you need a Data Warehouse even if it's small.
There's a ton of different ways in which you could perform the required steps, but a combination of an ETL tool like SSIS or Azure Data Factory and stored procedures could work if you already have SQL Server at your disposal.
-
This design works assuming that you may want to add the costs of a product's ingredients, but only ever want to aggregate the selling price of products by using an average across time
The only ways I was even thinking of using that "Recipe" fact table was to do simple stuff like a simple SUMX to get the unit cost of a product over time and maybe MINX, MAXX, AVERAGEX. Nothing more complicated than that. (I mean, how much can you do with that one attribute??!!)
I'm really looking at growth rates or rates of cost increase over time. To some degree, I'm trying to answer the question: "At what point do I need to raise my selling price to make a reasonable profit?" or "At what point is this just no longer viable because prices are just going insane, so even though I'm selling x quantity of product each week, I'm barely breaking even?"
The only other question I would even try to answer are capacity questions... (How much product can I produce in a timespan?) But that has nothing to do with the complexities of price changes. That's just simple math.
-
Phil,
Not sure I totally answered your question before... To make a long story short(er), I'm basically trying to see/analyze/understand how profitable the business is over time (with the crazy grain and gasoline prices). I already did one model where I can adjust the prices of the two and see the effect on profit - the two unit prices are just slicers/measures in my model.
I have a Sales fact table (but I need to change things so that I show different "versions" of the product as the ingredient prices change). Right now my not quite correct version of sales is (ProductKey, CustomerKey, DateKey, TimeKey, Quantity). The ProductKey is a bit simple as I haven't yet turned Product into a Slowly Changing Dimension, but I think Martin sorted that out for me in his design sketch.
The overarching goal of all this is to see how the increasing costs of ingredients (particularly grains and flours) is affecting overall profit. So there's Sales on one side, and then the versioning of the Products across time (as ingredient prices change), which relate to Recipes (which change because it's a bridge table with a single attribute, RecipeWeight). The whole "Expenses" bit is just something I would spread over a time period (like a quarter), because the math and logic is easier.
If I missed anything, feel free to ask more questions.
-
Thank you for elucidating. It's a really interesting problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I think I understand how to do it now. Running totals of Sales Amount (Gross Revenue) is straightforward. With the SCD2 of Product, I can store the unit cost of the ingredients used (that's what the SUMX() thing was all about). Then subtract and you have the gross margin (well, divide by yield, but who's counting?). Then I can graph the margin over time etc. That's pretty easy now (I think).
The hard part was getting my head around "the recipe changes all the time" problem, because each week, it could be pointing to the same basket of ingredients, but at different price points. But Martin sorted that part out for me. I think I have to just dump all my data into SQL Server and then it won't be so hard. (Except I need to be able to call PowerQuery to parse the Costco Invoices so I know how much he's paying for most of his ingredients. I think I can do that in SSIS with the PQ add-in or whatever.)
What I wanted to be able to see was maybe a stacked (and unstacked) bar or line chart of [Unit Price] * [Weight] for each ingredient over time, so that any lines with a crazy slope would be really obvious. I *think* I understand how to it now, but it's PowerBI, and almost nothing to me in PowerBI is straightforward. (PowerBI may make some things possible, it doesn't mean it makes it easy...well, at least not in terms of modeling!)
-
A quick thought, let's figure out what needs to be done before pondering on how things are done!
😎
A simple modelling of the problem would include the following entities:
Entity: Units
Type: Static Dimension
Role: Normalise weight and volume material measures
Relate: One-to-many
Entity: Material_Price
Type: Slowly Changing Dimension
Role: Reflects the changes in material prices
Relate: Many-to-many
Entity: Material
Type: Static Dimension
Role: Contains all static attributes for the ingredients (materials)
Relate: One-to-many
Entity: Recipe
Type: Slowly Changing Dimension
Role: Which materials and quantities are needed for a Product
Relate: Many-to-many
Entity: Product
Type: Dimension
Role: Definition of the static attributes of Products
Relate: One-to-many
Entity: Sales_Lines
Type: Granular Transactional Fact Table
Role: Sales Items
Relate: Many-to-many
Entity: Sales
Type: Fact
Role: Aggregation of sales items for each transaction.
Relate: One-to-manyERD:
This can then easily be aggregated for further analysis.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply