Frank_Wade, I am unfamiliar with that application, but the thing about databases is that they are the “bones”, the “structure” - a body upon which you put on clothes (less gruesome image than “flesh”).
In another life, when assisting callers with their database designs, I could see the structure behind what they were doing and pull a database off the (mental) shelf. The User could only see their specific implementation. But a database to keep track of payments to a dating site might be the same as one keeping track of church donations, and perhaps the same as totaling times in a multi-event track meet. Each person had a “unique” situation, but the underlying database structure was the same. An individual was identified, amounts (time, dollars) were accumulated - with a date component - and summaries were generated.
When I read that you plan on adding formulas in the tens of thousands, it triggers a large orange warning sign in my head. You can apply ONE formula to tens of thousands of records at once.
I’m not familiar with the language of your project but it sounds like you would be using the PanX GROUP ability. For example, If I had three movies and I wanted to track income from the snack bar for each, I could input the revenue with the “unit” being a dollar amount for a specific snack item during a specific movie on a specific date. So, date, movie title, snack item, and price - maybe quantity, price each, and total price.
Using PanX’s Group- ability, I could “roll up” those figures and see the total revenue for each snack item for each movie, with further breakdown by date. I could wrangle it any way I wanted - say just the totals for each snack item. or totals on the weekend compared to mid week, etc.
Can you see the bones? Can you see that be it a movie with a snack bar or an Aeroplane with “Parent Items” that have individual daily cost items - child items - you can group the child items and parent items and with one of two actions, see the child totals and the parent totals of the children.
PanX allows for 7 summary levels. You mentioned “…9 hierarchical levels?” but as mentioned before, you may not need to keep all the data in one database. You could, for example, have a couple of databases for various items, summarize those, and then bring those summaries into more of a “master result” database.
Here’s the image in my head. I am tasked with giving 5 juggling balls x amount of air time. I can try to juggle 5 at once (which I don’t have the skill to do), or I can juggle 3 for x amount of time then two for x amount of time. By the way, “time” in PanX can be fractions of a second.
Finally (for now) understand the language … gap. When you say your BOM model can handle only 32,000 variables, it might be that what you are thinking of as “variables” might not be what I am thinking of as variables.
I’m starting to get a feel for this now - if I’m on track. Let’s say you want to build [something] and that something has various parts and labor cost could also be a “part”. And at the end, or any time during the project, you want to see the total quantities/costs, for those parts.
Yes, you can absolutely do that and you don’t need to write tens of thousands of formulas.