As described elsewhere, a key architectural capability of ClearFactr is to allow you to connect your models to external data, held in Cloud Data Warehouses.
The specifics differ slightly between different CDWs, but the overall process is the same.
Read More
Adding one or more queries to your model involves two steps, the first of which only needs to be done once:
This a specially-permissioned feature. Ask your account system administrator (or the ClearFactr Customer Success team) about it. If you have permissions to this feature, the DataSource Editor will appear on the Developer menu visible on any open model. Select the item and you'll see the following:
Click here to learn more about each section of the tool.
You only need a single person on the team (with perhaps a backup) to create and manage your DataSources. All other users can make use of them, as described below:
ClearFactr's propriety CFSQL function is the connector between the details of your model and the data in your Cloud Data Warehouse. You can certainly write one by hand, but it may be easier to use our Query Editor to do it for you. Yes, you can think of it as a "wizard for CFSQL". Once you get your query dialed in, and tested, via the Query Editor, you just commit it back to your chosen cell, and the results come into the grid. If you ever want to change the behavior of the query, just double-click the cell as if you're editing a formula, and the Query Editor will open with your CFSQL configuration already in place. Notice we said "behavior" of the query -- not the inputs. The most powerful thing you can do with this feature is to have cell values from your model, even those computed or derived by other queries, drive your chosen CFSQL function. In other words, there's incredible power in making these things highly dynamic and interactive.
To get started, right click on a cell and select "Insert --> Query" from the context menu. That will bring up the Query Editor, where the very first step will be to select a DataSource. Here's the Query Editor:
Click here to learn all about it.
Even within a Single Source of Truth, you might have variations of a given model that are only different in minor ways. Perhaps 95% of the business logic wants to be the same between ten different models. Coding up those variations between all the different versions can be done within the formulas themselves, but it will lead to all kinds of conditional logic that increases the size of the spreadsheet, slows down its compute speed, and makes things harder to understand.
Read More
Within any given model, performing sensitivity analysis, or creating and comparing scenarios can be complicated – so much so that many (most?) people simple don’t do it. With ClearFactr, these things are built-in and support simultaneous, collision-free workflows. You’ll wind up with models that are smaller, faster, easier to understand, easier to maintain, and present dramatically less operational risk.
Have you ever been asked to make a copy of a spreadsheet for someone, only to wonder later if they’ve actually done anything with it? If ten prospects asked for a quote on the same thing, who should you follow up with first, and how? Have you ever forgotten who actually has access to the spreadsheet, and with what type of access?
Read More
These are just some of the reasons ClearFactr provides a Model Engagement panel for every model that you have access to. Some people refer to this panel as ClearFactr’s “Virtual Data Room”, recreating how the manager of an old-time physical data room might control who has access to what, who’s been in the room and for how long, what items in the room they seem to be interested in, etc.
This panel is also the place where fine-grained, per-user, per-model permissioning is actioned. Roles for users span from read only, through limited editing, to the ability to make structural changes and more, and also include the abilities to merge scenarios and export the model back to a native Excel file. Management and auditing of the latter is crucial as this an opportunity for data and business logic to be transferred from a highly-controlled environment to an uncontrolled one.
From the moment it was easy to organize data into the rows and columns of a spreadsheet grid, it was easy to use spreadsheets for data management. Throw in breakthroughs like import/export and copy/paste, and suddenly you could never have enough rows in a spreadsheet file. For a single maintainer and user of that data, perhaps this was truly incredible. But what if someone else wanted that same data, not just to enforce a Single Source of Truth, but for an entirely different purpose? And what if that purpose was computationally intense, something that everyone agreed would ideally NOT be done in a spreadsheet? You could move the data to a database, but then where would it leave the spreadsheets and the value they were delivering to their audiences? And who would do the custom coding to and from the database, and what might that cost, in time and money?
Read More
A common use-case for legacy spreadsheets involves processing a portion of a much larger dataset that is held within one or more sheets/tabs in the model. Comparatively, the actual calculation portion of the workbook is only a small fraction of the total number of cells in the model. Many of these cells contain conditional logic that serve to filter down the portion of data being run through “the model.” There may even be intermediary stages of processing performing this filtering and aggregating in successive stages, physically structured across multiple sheets. Over time, more and more data might get added to various portions of the workbook. The entire workbook itself might get copied into multiple files simply to isolate certain datasets and/or for file-size reasons. Once that stage is hit, maintaining a consistent model across multiple files becomes increasingly difficult.
With ClearFactr, all audiences and consumers of that data can be happy.
ClearFactr’s approach for this situation would involve moving as much of this data into one or more tables within one or more databases in a Cloud Data Warehouse.
When CFSQL returns the data in the form of a two-dimensional grid, additional modeling with that data can be performed via traditional formulas and functions.
Contact Us to see this in action.
Imagine a large piece of custom software developed and delivered over a long period of time. As the needs of the business change, so will the software. Many teams pride themselves on being able to turn around simple fixes or upgrades in a day or two, but many still take weeks or more. And then you might have smart, curious end-users who always wanted to better understand “what the system is doing, ” and even to fool around with those behaviors in a safe manner. They might even try to replicate part of it in a spreadsheet!
Read More
With ClearFactr, the end-users and the custom software developers can decide what parts of the overall “system” should be safely exposed to the end users through a familiar spreadsheet interface – and yet still drive that same larger system. These portions of the overall system are implemented via one or more ClearFactr models, and the custom software interacts with these models via the ClearFactr APIs.
Then, with fine-grained permissions and full auditability, the end-users themselves can modify portions of the overall system, simply by making changes to the ClearFactr model(s). The moment a new version of the ClearFactr model is saved – with approvals as necessary – all users of the overall system will start to get the corresponding new behaviors. All of this is done without needing to annotate the model, register it into some central service, recreating API endpoints/services, possibly recompiling the larger system, etc.
How many times have you seen a model with “base”, “best” and “worst” cases, or variations thereof? And what did it take to get the model to compute and compare those different cases? Many times, especially with highly complex models depicting conditional events, simple variations just won’t properly explain what could happen, or the chances of it happening. Then, getting the inputs just right so that some optimal base-case was computed, perhaps subject to some constraints, can represent a nightmare for the spreadsheet developer, and maybe even the custom software developer.
Read More
Monte Carlo simulation and optimization has been a long-standing solution to these needs, but it was typically only available to the spreadsheet user via expensive plugins. Great for you if you were able to buy that plugin, but possibly game-over if your spreadsheet audience wanted that same power, but didn’t have the plugin.
Because of the unique power of these tools, ClearFactr has had them since day one. Like the other ClearFactr analytics, every user can use them simultaneously without affecting other users.
Contact Us to see this in action and learn more.
"In the midst of chaos, there is also opportunity."
Sun Tzu, The Art of War (circa 5th century BC)
"Chaos is merely order waiting to be deciphered."
José Saramago, The Double (2002)
"The entrepreneur always searches for change, responds to it, and exploits it as an opportunity."
Peter Drucker, Innovation and Entrepreneurship (1985)
"Out of clutter, find simplicity. From discord, find harmony. In the middle of difficulty lies opportunity."
Albert Einstein, The Ultimate Quotable Einstein
"Chaos often breeds life, when order breeds habit."
Henry Adams, The Education of Henry Adams (1907)
"In the midst of chaos, there is also opportunity."
Sun Tzu, The Art of War (circa 5th century BC)