Data Hub to Spoke Model Performance Question

We are building a budgeting and reporting model based on transactions incorporating our chart of accounts. We have built a Data Hub to house the lowest level transactions. Currently we budget using 6 categories with several layers of hierarchy totaling a count of 6,475 items. Summarizing monthly transactions we are looking at loading approximately 374,619 rows of data lines from csv files into the Data Hub annually and accumulating three years. We then will pull that into our spoke model to report on and allow for budgeting and forecasting. We are concerned that our cell count is too high and are struggling with how to lower it. Do you have recommendations to lower this in our spoke model? We have tried to keep our formulas simple and follow best practices. Is it reasonable to have the quantity of categories, hierarchies and data lines in our spoke model in order to complete our budgeting at the lowest level hierarchy?

We’ve included a screenshot of our spoke model including the module showing 398,697,600 cell count.

Thank your assistance with this.

Accepted answers

rob_marshall

@ebarlow21

Wow, you may have hit the trifecta on potential "opportunities". First, 781M cells is not that big as each line item has a threshold of roughly 2.1B cells. With that said, that is what you "can" do, but that does eat into your overall footprint. I would suggest a couple of things:

do you HAVE to have the lowest level of detail within this model? The reason I ask is usually the detailed data doesn't, but it is used for validation. With the way the UX is built, this detailed data can be in another model for people to view, it would just be another page within the app (assuming you are using the NUX).
a ton of space is often eaten up due to summaries of line items being turned on (they are turned on by default). Often, these summaries are not needed, so I would review where they are turned on and see if they can be turned off.
Another article that may help is Model Optimization which gives you things to look for (link)
Speaking of splitting up the modules, you don't have to have everything in one module, think of this as progressive disclosure, only use the dimensions that you really need. For example, if you are doing variance analysis, you really don't need the Versions dimension, you can use line item subsets and line items for the versions (link).
Lastly, I will leave you this article which is really good and talks about sparsity (link)

Hope this helps, and Happy Holidays!

All comments

abhay.kanik

Do you need the data at transaction level in spoke model for planning? As per my experience transactional data isn't necessary for planning its the aggregated data which can be used adn if thats the same case with you i would recommend to aggregate the data in data hub as per your required hierarchy and then import it into the spoke model.

also ~400 mil isnt too big for a model.

ManjunathKN

Hi @ebarlow21

If you want to do budgeting only at the lowest level then there should be no problem with your hirearchy as you are going to keep your summary NONE(list/line item summary). Understand the reporting requirement if the values are required at any hirearchy level use that hirearchy to a reporting/output module.

If all of your levels in hirearchy is required to display then it is better you use the calculation module as the output module. Sizing should never be an issue if best practices are followed.

Thanks,

Manjunath

MarkWarren

I'd echo this great point from @abhay.kanik , why do you have the transactional data, especially the flat list in the spoke model? What is the data hub doing?
It should be processing/aggregating the flat data into what is needed in the spokes...

ManjunathKN

@ebarlow21

Your tans01 module is having org list and accounts and why do you named it as trans01 module.

Firstly, you should not bring the flat list(transaction list) values/properties(line items) again in spoke model. You should do a import from data hub through a saved view of that transaction module. When you bring data from data hub, you should bring it in 2 different modules, one for properties(which does not change by time) and other for data with time.

For calculation purposes you should use data module and for mapping purpose you should use properties module.

Now i see 2 potential mistakes (I cannot confirm without seeing blueprint view) 1. you might be bringing the data for trans01 module from dat01 module, which is wrong and you should do like I explained above.

2. You might be having/included the text formatted line items in trans01 module with time dimension.

https://community.anaplan.com/t5/Chapter-2/2-01-13-Separate-Transaction-data-from-Attribute-Property-data/ta-p/80736

Thanks,

Manjunath

ebarlow21

Thank you for the previous information. Can you further explain how to aggregate and disaggregate data in the Data Hub?

In addition without using the Transactional Flat list to import, how do we create the import when we have 6 independent (non-hierarchical) lists that we have to map to in the spoke model?

rob_marshall

@ebarlow21

Have you read this article, it is pretty good on explaining the use of Data Hubs.

https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-peak-performance/ta-p/48866

ebarlow21

Hi @rob_marshall ,

We have read this article and implemented it in our Data Hub, which is working well. Our spoke model is still causing us some issues due to size. We've read all the best practice articles, incorporated many of the recommendations to reduce sparsity and size, but still looking at a very large model.

Our user story requires account details by organization at the lowest hierarchy level and funds at a higher level. The org list is what is driving the cell count as far as we can tell. We have time and the 3 lists set up so that accounts are in the rows, orgs and funds are in the pages as drop down items. Versions is in Columns - budget actual variance and forecast.

We have tried to split the funds and orgs up into separate modules, but can't figure out the best way to combine them back for ultimate use in the UX page. We built the model with Orgs and Accounts only and the UX page worked great but we need to add the Fund list in and that gets us upwards of 781M cells.

Do you have any advice or other articles that may get us over this hump?

Thanks and Happy Holidays.

Erin Barlow

rob_marshall

@ebarlow21

do you HAVE to have the lowest level of detail within this model? The reason I ask is usually the detailed data doesn't, but it is used for validation. With the way the UX is built, this detailed data can be in another model for people to view, it would just be another page within the app (assuming you are using the NUX).
a ton of space is often eaten up due to summaries of line items being turned on (they are turned on by default). Often, these summaries are not needed, so I would review where they are turned on and see if they can be turned off.
Another article that may help is Model Optimization which gives you things to look for (link)
Speaking of splitting up the modules, you don't have to have everything in one module, think of this as progressive disclosure, only use the dimensions that you really need. For example, if you are doing variance analysis, you really don't need the Versions dimension, you can use line item subsets and line items for the versions (link).
Lastly, I will leave you this article which is really good and talks about sparsity (link)

Hope this helps, and Happy Holidays!

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…