Relatively new user here (Dec 2023) and I’m curious if a reasonably comprehensive definition of the underlying data model used by Tiller is available?
I’ve been happy with the feeds and the collection of data within a single spreadsheet. The templates and community work have been both helpful and inspiring to me in getting value from the product and build my own solutions for my personal use.
However, I feel like my learning could be accelerated, and pitfalls avoided, if there was documentation around how data is defined and used within Tiller sheets. What I have been able to determine I’ve largely done so through reading support articles, browsing the community forums, and through trial and error.
For example, it seems mostly obvious that the “Accounts”, “Transactions”, and “Balance History”, worksheets are the primary stores of feed data for Tiller. Within each are primary keys (“Account ID”, “Transaction ID”, and “Balance ID”) as well as foreign keys for the other worksheets. Given this information you can do all sorts of great analytics and visualizations.
What surprises me is that this information is not documented and shared with exuberance across the community. Even worse, there’s at least 1 support article (Understanding the Balance History Sheet | Tiller Help Center) that ignores this information and gives really awful advice on how to import historical data into the “Balance History” worksheet WITHOUT regard for a primary key (a blank value is not unique for a primary key). If you follow the instructions within this article, YOU WILL compromise the integrity of your balance history. Ask me how I know…
So my question to the Tiller Team and the Tiller community - isn’t the underlying data model critical enough to maybe document it more cleanly and promote it more prominently amongst the documentation? Shouldn’t there be a verification that existing help articles are not violating this model in the advice and instructions given?
Honestly the only sheets that matter are the transactions sheet and balance history sheet. They are the core sheets thattiller provides. Everything else is a derivative of that data. The data model is just the fields that they expose.
What @richl said. I don’t think Tiller has a “data model.” The only data is transactions and balances, which are simply imported as raw data. Now, there may be an issue with importing historical data (I haven’t tried so wouldn’t know), but that would be a process issue, not anything about an underlying model.
So to clarify, I’m not saying there is anything wrong with the underlying model - just that it isn’t well documented.
And there clearly IS a data model. I don’t believe Yodlee provides “Account ID” (primary key of the Accounts worksheet), “Balance ID” (primary key of the Balance History worksheet), or “Transaction ID” (primary key of the Transactions worksheet). Tiller does. They also use foreign keys in the Balance History and Transactions worksheet (“Account ID”) to enforce the reference.
So somebody gave this some thought. I just wish they would put those thoughts into an article.
And fix the “Understanding the Balance History Sheet” article since the process described in there will lead to duplicate primary keys and will really mess up your use of this data outside of Google Sheets/Excel.
I have been making excellent progress on an AppSheet mobile app for Tiller (Accounts linked to Transactions and graphs of balance histories per Account all in a tidy app) - but not having a cleaner understanding of the keys and data structures required a lot of extra time and ultimately, a lot of data cleanup. Good news I fixed it and things look very promising. I hope to share with the community soon. But I suspect other attempts by community members to leverage this data outside of a spreadsheet will be thwarted by some of the Tiller nuances we don’t fully understand.
Ah, I see what you’re saying. Sorry, I read “data model,” and I think of a model that Tiller is using to manipulate the data to answer some question. (I’m a social scientist, and that’s what a “model” means in my world.) You’re asking about how the data is structured and engineered to fill the Tiller sheets. I could see how that would be useful information for what you’re trying to do. I wonder if Tiller sees that data engineering as proprietary—part of what makes Tiller work—so they wouldn’t put it out there publicly.
jeffh0821 brings up a great question and makes several good points. I am not as skilled in area of Excel Data Model, but I have run into what appears to be a related situation. I have created a Pivot Table that I use for profit and loss analysis. At one time, I could click on a particular monthly total item in the resulting pivot table and all of the transactions that make up that total would appear in a separate dialogue box. After considerable manipulation of both the Transactions and the Balance History tables, something has occurred that has broken the link(s) that derive the individual transaction entries and I get a ‘data model’ error. See attached. I want to experiment in trying to re-establish the links but not sure where to start. Any suggestions would be much appreciated.
