Jim Grant, editor of Grant’s Interest Rate Observer and financial news contributor (you may have seen him on CNBC), recently shared 10 of the most important lessons he’s learned in 35 years in financial markets. Reading through his list, I observed several parallels to experiences and scenarios in business intelligence (BI) that collectively could comprise this week’s blog post.

Note: Though hands-on MSBI design and development topics are the normal subject matter, view counts suggest that some readers appreciate non-technical, higher level content and a broader perspective. 

Ten Lessons in Finance (and BI)

For each lesson I’ve provided the essential meaning from a finance/investing perspective and, if necessary, you can use the link above to Zero Hedge to read the note in full. For certain lessons I admit I’m stretching a bit either because I’m not aware of a direct application to BI or because there’s a particular view (not an SQL view) I’ve wanted to share.

1. The key is having everyone agree with you – LATER.

From a finance perspective, this essentially recommends being a contrarian and to go against the grain by favoring investments that are currently unpopular and avoiding various momentum investments (e.g. dot com stocks in late 90s).

From a BI perspective, it’s a good practice to review the latest Release Notes and you may also occasionally review the status of several ideas for Power BI. However, it’s much more valuable, eventually, to take actions now which may be unpopular or not immediately useful but align with a future state. An example of this might by the Analysis Services pro learning Python or R and data science and then later benefiting from a new feature that integrates or extends Analysis Services Tabular models to support data science. Alternatively, the same BI pro could target the data transformation layer/phase by investing time in Spark SQL and a tool like Azure Data Bricks that ‘may’ become the norm in cloud data warehousing architectures.

I fully expect Power Query (M) to become a required skill for many MSBI jobs/roles over the next few years. It could become as pervasive as PowerShell is for administrators today.

A historical example at the macro level could be Microsoft making large investments in cloud infrastructure and technologies well before it was completely clear that organizations would migrate to the cloud.  

2. You aren’t good with money.

From a finance perspective, the meaning is that humans naturally act on emotional impulses – they sell stocks when they’re actually cheap (fear) and buy stocks when they’re expensive (euphoria). The recommendation is to eliminate all emotions from the decision and rely on detailed security analysis.

In BI, you often see an overreaction (positive and negative) to a temporary event – one that masks a future state. Let’s say you have a Power BI or Analysis Services model with 150 DAX measures and it’s determined that 1 or 2 of them don’t include the business logic that was intended. As a result the business users or stakeholder may choose to not use or trust the overall solution – even if 95% of this content could actually be of value.

Another example in the opposite reaction could be an overvaluation of a solution or one’s knowledge. A project has been successful or a particular tool has been effectively utilized and thus THIS project becomes the new template/standard to follow or the BI pro is considered an absolute guru. This enthusiasm may not be completely warranted and thus trouble could lie ahead.

3. Everything about investing is cyclical…

Grant advises that “The greatest investors develop a sense of when markets have reached euphoric levels. And of when fear is crippling reason.”

A takeaway from a BI perspective could simply be to get used to the ups and down rather than extrapolating based on the current trend, similar to Lesson #2. Let’s say you’ve successfully deployed Power BI to 2-3 functional areas (ie Finance, Sales) within the organization and adoption is high. (By ‘deployed’ I’m assuming that the BI/IT team is responsible for the semantic model and ELT/ETL processes, and possibly a few specific reports or dashboards).

A number of factors, however, could make a future project, such as for the vendor management or marketing, much less successful. It could be that the Finance and Sales teams already had enthusiastic power users or support from a key stakeholder that helped drive the adoption. Maybe the data sources and requirements of the vendor management or marketing teams imply a completely different solution such as DirectQuery model and complex calculations that could present performance issues.

On the other end of the spectrum, the initial deployment to Finance and Sales may have been less impactful than what was hoped. The business teams, for example, are still using their own internal data retrieval processes and Excel-based solutions. The cause of this could actually be something relatively easy to fix, such as a few new columns or measures reflecting the logic of their existing process/system. Once implemented, the users may begin to appreciate all the other benefits that come along with Power BI.

4. You can’t predict the future. Nor can the guy who claims he can.

Grant’s lesson suggests that you can recognize rhythms of market cycles and thus anticipate changes such as observing subprime mortgage debt defaults in 2006.

For this lesson I’m going to stretch a bit and just infer that it’s healthy and realistic to accept that everyone has certain blind spots or knowledge gaps. Let’s say you work extensively with T-SQL, DAX, and Power Query (M), perhaps you’ve invested time in building query sample solutions for these languages as described last week. Well, as a consequence of pursuing more of a ‘full stack’ BI developer role, in all likelihood you don’t have the equivalent depth of knowledge as someone who focuses on one of these languages. Likewise, the single-language expert, let’s say it’s DAX, should understand the limitations of DAX in certain scenarios (e.g. calculated columns and tables, virtual relationships) and/or the importance of the other languages to address these issues. (In other words, because a technique ‘works’ with one language doesn’t make it the right or only way of handling the same problem)

Another related BI phenomenon from this lesson is the sometimes obsessive pursuit of an external resource as an absolute and comprehensive authority on a given topic. Whitepapers are great, so are books and blogs, so are certification exams (I personally like the exam training materials (e.g. 70-767 for DW and 70-768 for Data Models), so are tech conference/event presentations, so are consultants and MS partner firms, and so is MS docs, etc. Usually these sources align around fundamental topics and some delve deep into particular areas. However, ultimately they all have certain limitations and there’s much less clarity and cohesion among when you get deeper into details. As one example, the Modeling for AS Tabular Scalability whitepaper recommends value encoding for all numeric columns, particularly surrogate keys, while MS docs for column properties recommends hash encoding for foreign keys. There are best practices and patterns obviously but there’s also some degree of uniqueness that results in some minimal level of uncertainty in the proper or optimal solution.

5. Every good idea gets driven into the ground.

In terms of finance, Grant uses exchange trade funds (ETFs) as an example of an investment product that does represent a good idea (low cost diversification) but one that has been taken too far in currently accounting for 23% of all trading volume.

This happens in BI all the time. Let’s say that a paginated report is developed for SSRS or the Power BI Report Server that integrates two separate datasets reflecting two distinct data sources via lookup functions. Additionally, let’s assume that lengthy, complex SQL queries are used for each of the report’s datasets. The developer did a good job given all circumstances and the report meets its requirement and use case. However, now the business knows that this kind of report can technically be created and thus can pressure BI/IT to quickly produce many additional similar reports rather than hold off for a more sustainable approach (DW, ELT/ETL).

Another example that comes to mind is using cube formulas in Excel against an Analysis Services model to replicate a business user’s existing Excel report. I recall one of these reports in which user selections for start and ending periods (via Excel combo box control) were passed as inputs to the cube formulas to make the report interactive with the Analysis Services source. Anyway, it was another technically good idea for its specific use case – a particular template style report, but it wasn’t something that you would want to have replicated across many pages or reports.

6. Markets are not perfectly efficient.

By this Grant suggests that asset prices don’t solely rest on business or economic fundamentals because the humans responsible for valuing assets aren’t perfectly reasonable. In other words, opportunity does exist for active management.

In BI, not every decision is made based on the facts and no amount of evidence or persuasion can redirect a decision. Let’s say that an assessment finds a high level of tribal knowledge between and within teams on data sources and definitions and that no metadata management system exists. Given this background and other factors the consultant recommends Azure Data Catalog and walks through how this tool can help. However, though the business may appreciate the suggestion, ultimately they may not adopt it just because it’s something new or because it’s something other than their current focus, such as Power BI.

Likewise, I’ve put together detailed cost analyses and supporting materials to firmly recommend Azure Analysis Services over SQL Server Analysis Services (SSAS). In some cases, despite the clear benefits, the organization still went with SSAS. This can be a bit difficult to accept but I guess the takeaway for BI is to just keep focused on the variables that can be controlled or known and less on the human psychology or corporate culture elements.

7. Patience is the highest yielding asset.

Grant references Charlie Munger of Berkshire Hathaway, who in turn attributes most of Warren Buffet’s success to endless hours of reading.

In a BI sense, patience with Power BI has been very rewarding. I recall meetings in 2016 in which so many reasonable requirements just weren’t yet supported (e.g. conditional formatting). Additionally other competing platforms had certain advantages over Power BI that have since been erased.

Stepping back a bit further, early adopters of DAX, Power Pivot and the Tabular model have been rewarded for the now enterprise grade capabilities available (and more coming) and broader adoption. I recall two separate projects using SSAS Tabular 2014 (1103 CL) in which the limitations of the product at that time (no NUMA awareness, no bidirectional relationships, etc) created a temptation to switch over to Multidimensional models. (Without patience, those companies would have Analysis Services models that don’t have a cloud option (currently at least), don’t have Power Query, and are fundamentally misaligned with Power BI datasets).

8. Never stand in line to buy anything.

Similar to other lessons, Grant recommends caution when you find yourself surrounded by people who agree with you. You might be buying something at its peak or selling at its low point.

Maybe a suitable BI parallel is adopting the latest, most popular BI/data technology, despite its price and despite an unclear use case. It wouldn’t be admitted but at least part of the motivation is just to ‘feel’ or to be perceived as cutting edge. For example, some BI/analytics teams have been burned by the expectation that a data lake could replace the need or benefits from a data warehouse like Azure SQL DW. Another example could be the cost of data visualization platforms but given the length of this blog we’ll save this topic for another time.

9. Leverage is like chocolate cake. Just a little bit, please.

By ‘leverage’ Grant is referring to the use of debt to buy assets and thus magnify potential gains (and losses).

The parallel here to BI should be obvious – technical debt. I wrote a blog post on this topic a while back but one clear example is an excessive dependency on Power Query (M) transformations and DAX calculations to address issues that should be handled upstream in the data warehouse or data prepping/cleansing phase. In many cases, a few M transformations makes perfect sense to help push a project forward, particularly if the transforms can be folded back to the source system. However, sooner or later these more lightweight approaches can add up and become more difficult to manage and can also create version control issues with the source.

As mentioned earlier, I think Power Query (M) has an important role to play in the future of MSBI. Dataflows and Intellisense support in Power BI Desktop are just two of many steps down this path.

10. Don’t let want you want to happen influence your judgement.

The financial meaning here is, again, to eliminate emotional and psychological factors – to let the facts lead you.

It’s often the case in BI that certain tools or techniques become so familiar and comfortable that certain facts are ignored or improperly valued in order to justify this same approach or tool. Let’s take the import versus DirectQuery decision as an example – many SSAS pros are (understandably) more comfortable with the default in-memory mode (import) for its advantages in performance. Support for Composite Models and Aggregated tables, however, is going to present other design options which, at least partially, leverage DirectQuery sources. The diligent BI pro should thus give these new options and even a fully DirectQuery solution a fair consideration relative to the business requirements (e.g. data freshness) and available resources.

Wrapping Up: Power BI World Tour Seattle

I’m presenting at the Power BI World Tour in Seattle next month regarding the processing (refresh) of Analysis Services Tabular models. This will be just a few days after my ‘Husker Power BI’ presentation at SQL Saturday Lincoln, Nebraska. These events and ongoing BI projects (Frontline Analytics) may limit the volume and depth of any blogs over the next few weeks.

Thanks for visiting and consider subscribing (‘follow’) to get notified of future posts.