As I work with organizations in solving their Business Intelligence challenges, I often find that business logic is buried in various spreadsheets, stored procedures, and report definitions. Typically, this has not occurred by design but as a result of end users and IT professionals solving business information needs in an ad-hoc manner without a cohesive strategy.
Many times there is very minimal documentation of business logic and significant time is spent understanding and extracting the business logic contained in the spreadsheets, data base procedures and report definitions. This has led me to realize the importance of a creating and maintaining a Business Intelligence Semantic layer such as that highlighted in the graphic below provided by Microsoft.
By creating this layer of business intelligence calculations, key performance indicator definitions, and other measures, organizations can promote consistent data reporting, reduce data redundancy, and provide a more efficient reporting environment that can be leveraged across toolsets.
I often refer customers to the Microsoft Analysis Services & Power Pivot blog, which provides a detailed elaboration of this topic.