Capitulo 5. Diseño DWH - El Modelado Dimensional (FK-MODI)

En este capitulo veremos como se crea el documento denominado "Modelado Dimensional" dentro de la etapa 5 (Diseño y arquitectura del DWH). El desarrollo de este documento servir? para representar los requerimientos de los usuarios desde el punto de vista de un modelo dimensional y a su vez sirve como entrada para desarrollar el m?delo lógico de la base de datos (DM o DWH).


http://blip.tv/file/get/Falconeris-Capitulo5DiseoDWHElModeladoDimensionalFKMODI590.mp4

Five Simple Steps to Better Decisions

Business processes seem to come in two flavors: those that produce transactions or content and those that produce decisions. The quality of decisions from the latter category often drives the trajectory of the business. Well-executed, insightful decisions can lead to superior results.

1. Focus on Processes that Matter to Your Business

Organizations improving insightful decision-making carefully pick the key processes and operational variables on which to focus. Clear alignment exists between a successful organization's market strategy and its processes and operating metrics to implement the strategy. W. Chan Kim and Renee Mauborgne, in their groundbreaking book, "Blue Ocean Strategy," developed an interesting approach in which they recommend picking operational variables in the context of strategy development. They point to well-known examples of companies with clearly differentiated strategies, including Southwest Airlines and Cirque du Soleil.

In my own work, particularly in the high tech electronics industry, I have seen clients select variables that include forecast accuracy, order fulfillment rate and inventory levels. Making planning decisions on a weekly basis at the SKU level based on insights across those variables resulted in tremendous improvements in all three.

2. Stay Focused on Your End Goal

The improvements you target should be expressed as changes in the specific selected variables. For example, if the goal is to reduce inventory by 30 percent, the initiative should fit that objective clearly. This kind of Deming approach of "you get what you measure" is well-documented, but it is surprising how many organizations do the first step without then taking the time to set clear objectives in the second. Deriving insights from a business process requires a good balance of freedom to efficiently explore information and decision alternatives coupled with a clear idea of the objective.

3. Ensure Your Data Supports Your Insights

Taking into account the processes, variables and objectives selected in the first two steps, the third step in improving decisions is to determine the readiness of your data and infrastructure to support the kind of insights required. Organizations often get caught in the trap of believing that their data or infrastructure are not up to the task and assuming that progress cannot be made without solving those issues. And yet, decisions must still be made, and it falls on business analysts to cobble together information manually and come to meetings armed with spreadsheets. These discussions based on suspect data often lead to finger pointing and fact questioning instead of insightful decisions. My observation is that if the data is suitable to drive these required, but often ineffective, discussions, would it not make more sense to leverage the data in a smarter way to derive insights more systematically and in a way that improves over time?

One of these techniques is to provide analytic reports showing all variations of a particular data field along with their owners. The process designers indicate which field variant is authoritative for a particular value, and technology can be used to manage the communication with other owners as they align their data. As alignment is achieved, the quality of the insights steadily improves. This "peer pressure" approach to data cleansing at the source is reminiscent of rating systems used for sites like eBay. There is incentive to getting the information right at its sources because everyone sees the impacts of good and bad data downstream.

This technique is distinct from the traditional approach of creating large data warehouses that attempt to consolidate schemas and provide highly cleansed enterprise data from a central source for driving analyses and processes. Many organizations have struggled with the data warehouse approach, in part because their businesses don't remain static long enough to even finish the warehousing project. In my own work, I typically leverage warehouses that are in place, at whatever level of completion, and then use the peer pressure approach to fill in the gaps and address new gaps as they emerge.

4. Parlay Processes & Insights into Smarter Decisions

The fourth step is to design and engineer the process and business analytic capabilities required to produce the insights and execute the resulting decisions. This work might seem straightforward, but it is fraught with subtleties and traps typically resulting from experience biases among the team involved in the work. For example, an IT team charged with deploying a company's business intelligence technology of choice would naturally focus on the reports required. The reports are a critical part of deliverables, but if the business analysts still have to manually transform the information and engage in offline or disconnected interpretation discussions spanning a company's functions, driving insightful decisions remains difficult.

Alternatively, if the team is adept at business software development that supports transaction or content production processes, the tendency is to try to develop analytical processes using the same methodology. This typically results in elongated development cycles and solutions that still miss the mark. Improving insight requires a careful combination of flexibility and context management in some kind of guided analytics environment, as opposed to an exact, step-by-step approach.

If the team comes from a process development or consulting background, the two traps I see most are biasing the work more toward the process than the result and producing one-time deliverables that may not transition well into an ongoing change vehicle.

While many of the skills of these teams are often highly valuable, agile development processes coupled with the right amount of business-focused domain expertise are more suitable for business analytics. Getting capabilities in the hands of the process stakeholders quickly and then letting them evolve as the methods of gaining insights emerge usually adds more value quicker than locking down exact requirements and following traditional development methods. And it is equally important to ensure that the resulting process captures the entire insight loop, including planning, reporting, analysis, collaboration, decision-making and execution.

5. Use Your New Processes to Drive Improvements

The fifth step is to operate the new process and drive the targeted improvements. Here, it is important to make sure resources are provided for properly interacting with the process, data and stakeholders to facilitate the emergence of insights and decisions. Initially, the new process might require more work than the old process, especially until the stakeholders get comfortable with the differences in the new decisions versus what they would have done in the past. This initial increase in work should be planned for, and if your program is successful you should soon see a much sharper net decrease in work versus the old process. The best insight-driven processes eventually require tremendous effort to stop, as opposed to tremendous efforts to keep them running.

This five-step approach blends a number of disciplines, and most organizations will not have all of the skills or required technologies readily available. You don't have to go it alone or wait to get started until your team is fully in place, though. Business models and other resources are emerging quickly to help organizations holistically with these kinds of programs and will be of tremendous value as you develop your program. As you move through the steps, you can define the gap between the resources you have and those that are needed and build your case around the target variable improvements that process insights will incrementally deliver to your business.

SaaS BI Tools: Better Decision Making for the Rest of Us

Simple business decisions, each of which impacts a company's performance and efficiency, are made every day, at every level of an organization, by workers in every department. But conventional business intelligence (BI) tools are often not available to most decision makers and are typically designed for use only by trained business analysts. Software as a service (SaaS)–based BI tools are designed to help the millions of people in non-IT "lines of business" (LOBs) who struggle every day with the task of mining Microsoft Excel spreadsheets and other unstructured data sources when performing everyday tasks such as making sales forecasts, planning for resource utilization, or servicing customer accounts. Especially in this time of limited budgets and uncertain futures, inexpensive, easy-to-deploy SaaS BI can help companies put easy-to-use data mining and reporting tools for smart decision making into the hands of more employees and uncover the real "geniuses" of decision making hidden in every department.


Benefits of SaaS BI

BI offerings delivered via the cloud provide tremendous additional benefits of scale and efficiency,lower cost, and better consumption of cloud and local data sources, and they are changing the way businesses license, deploy, and utilize BI to support decisions at their companies. Some benefits of SaaS BI are as follows:


Access by more employees to more data. Key beneficiaries of the trend toward SaaS BI have been the millions of people in non-IT lines of business who struggle every day with the task of mining Excel spreadsheets and other unstructured data sources when performing everyday tasks such as making sales forecasts, planning for resource utilization, or servicing customer accounts. Users of LOB applications produce the production data that drives BI requirements, and the powerful BI reporting and analysis capabilities are especially impactful in the hands of the users who created the data, resulting in greater adoption and utilization. Every business can be more efficient by putting better reporting and analysis tools into the hands of the LOB and departmental employees who are the subject matter experts in their domains. SaaS BI can make their jobs easier by providing browser-based access to sophisticated but easy-to-use data mining and reporting tools and uncovering the "geniuses" of decision making hidden in every department.

Business optimization for hard times. SaaS-based analytics can help companies be more resourceful in volatile times by helping them identify cost savings, efficiencies, and opportunities for process improvement they may have otherwise "missed in the data."


Faster "time to value" for a quicker return on investment. Implementations of SaaS BI solutions can be far faster and less expensive than implementations of conventional solutions. Consider that building a traditional BI solution with a data warehouse implementation, data normalization, and data marts for data staging by query systems typically requires between 6 and18 months, sometimes longer. By contrast, SaaS BI deployments typically require 2 to 4 months, and SaaS vendors cannot book revenue until the implementation is complete — a situation in which both buyer and seller are equally incented to decrease what some vendors call "time to value."

Streamlined architecture, with zero infrastructure. Unlike on-premises BI systems, SaaSbased BI is hosted by a vendor. Users access the various modules (for example, analysis,reporting) securely via any Web browser. From a systems architecture standpoint, this method is optimal because it does not impose an ongoing computing burden on back-office production systems, and because the application is hosted by the SaaS provider, users do not need to maintain an onsite data warehouse. Users conduct their secure sessions via a Web browser, so there is no client software to install, and users are always assured of running the most recent, optimized version of the application code because SaaS applications are not "rolled out" like conventional applications; they are simply upgraded and optimized on an ongoing basis.



Ability to tap operating expense (opex) budgets versus capital expense (capex) budgets. Because SaaS solutions are licenses as subscriptions, their license cost is a monthly, predictable expense and does not require a one-time up-front payment for licenses as conventional software. Further, the ongoing support costs to run associated hardware, management, and integration tools and middleware and hire and train staff members to support on-premises applications are substantial, and nonmaintenance support costs are typically booked as capex. Because these budgets will be flat in 2010–2011, SaaS solutions give users a chance to get access to BI and analytics tools much faster, using opex funds that might reside in their LOB budgets.

Better alignment of business goals. Business units consuming IT resources sometimes feel discordance between the technology they know they need to have to produce good business outcomes and the tools their IT staff has the skills and bandwidth to deploy. But IT is typically a cost center, and its priorities don't always align with LOB requirements. SaaS-delivered BI helps business units get business done and helps align the goals of the business unit with its technology tools.

Worst Practice #1: Assuming the Average Business User Has the Know-How or Time to Use BI Tools

Too Much for Too Few
BI report design, ad hoc query, and OLAP analysis tools have hundreds, if not thousands, of features. Although the user interface is often simple, complexity is introduced from the data side. Even a simple data warehouse has hundreds of columns of data, and it’s not uncommon for more complex systems to have thousands of columns. When an end user is faced with a blank canvas, thousands of columns of data, and hundreds of accessible features, complexity is automatic. “Where do I begin?” is often the first question, shortly followed by “I don't have time for this,” or “I give up.”

The user skill pyramid is a widely discussed and generally agreed upon description of the end users in most organizations. The simple version of the pyramid shown below demonstrates that 90 percent of the users within most organizations fit into the class of users known as non-technical business users, which means that only 10 percent of users are advanced enough to use a BI tool.


What may not be obvious from the pyramid is that most executives and managers, often the primary strategic decision-makers, are in the lower portion of the pyramid – that is the non-technical users.


It’s a Matter of Time
In some instances executives and managers are technical enough to use a BI tool, but they don’t have the time to work with a BI tool and navigate a data warehouse to produce the information they need. Most people need a faster, easier way to get the information they need than that provided by a BI tool.


BI Go-To Guys and Multiple Versions of the Truth
In some cases, moderately successful deployments of BI tools are found in individual departments. Usually that means that each department has identified and relies on a handful of advanced users who become the tool experts, or the “BI go-to guys.” These users employ the BI tool on the behalf of others, and create and distribute information for their department. In these cases, another issue is brought to the surface – the inconsistency of the answers generated by more than one
advanced user, also known as multiple versions of the truth.

Multiple versions of the truth result when two or more people apply different query methods and functions, and arrive at different conclusions. The challenge is that it’s difficult to know which, if any, conclusion is correct.

The tool-based efforts of advanced BI users do not go through the same rigorous quality testing of an IT department. Their work within a tool is typically not auditable. When this occurs, the validity of the information system, the BI tool, and the data warehouse are all brought into question. Valid or not, many companies have more confidence in operational reports generated, and tested by IT professionals. Many become skeptical of pure ad hoc information created with a BI tool because of the potential for variations and inconsistencies.

The Solution
Organizations need BI solutions that are easy to use for the entire user population, especially those in the bottom portion of the usability pyramid. In addition, they need a solution that mitigates multiple versions of the truth by providing access to a common source of enterprise information and standardized report generation methods. A BI platform is the answer to all of these requirements.

A BI platform leverages BI tools along with other technologies, including databases, data integration, and portals to provide an end-to-end solution for a defined business problem or set of business problems that can be termed a BI application. While BI platforms are implemented by IT professionals, their end result, the BI application, is designed for business users.

Organizations have been led to believe that BI platforms are too complex for their needs. This couldn’t be further from the truth. When you consider the data integration, warehousing, and end-user training costs associated with BI tools, a BI application built on a BI platform has about the same time to market as a BI tool. And end users embrace easy-to-use BI applications as part of their day-to-day routine, which is arguably the most critical success factor of any application.
This is why BI platforms have far greater success than BI tools.

The fact is that most non-technical business users can and will access information through BI applications, which are much simpler to use than BI tools. BI applications leverage reporting technology, Web browsers, and e-mail to make information more accessible to these business users in a comfortable, easy-to-use environment.

For example, today’s parameter-driven BI applications provide users a simple Web interface to navigate to the report they want, much the same way they would find an item on eBay or a book on Amazon. BI applications allow users to easily customize the report by selecting options from pull-down menus the same way they would fill in their address and select their home state or a shipping option from a drop-down list.

Users Trends (Business analysts)

On the other side of the equation, power users require MAD capabilities 20 to 40% of the time. The bulk of their time is spent using tools designed to handle a variety of analytical tasks, including report authoring tools, spreadsheet-based modeling tools, sophisticated OLAP and visual design tools, and predictive modeling and data mining tools.

Times have never been better for power users. Their desktop computers contain more processing power and can hold more data than ever before. Today, there are more tools designed to help power users exploit these computing resources to analyze information. Many cost less than $1,000 for a single user or can be downloaded from the Internet. “Power users have more power today than ever to perform deep analytics,” .

Despite the plentiful options, many power users are bereft of optimal analytical tools. Either they restrict themselves to spreadsheets and desktop databases, or that’s all their organization will give them. Most homeowners wouldn’t hire a carpenter with just one or two tools in his toolbox; they want a carpenter whose toolbox contains tools for every type of carpentry task imaginable. In the same way, organizations need to empower power users with a multitude of tools and technologies to make them more productive as analysts. If implemented correctly, the technology can liberate analysts to gather, analyze, and present data quickly and efficiently without undermining enterprise IT standards governing data, semantics, and tools.

Four types. Power users are a diverse group who perform a variety of analytical tasks. I’ve divided power users into four types:

1. Business analysts. Data- and process-savvy business users who use data to identify trends, solve problems, and devise plans.

2. Super users. Technically savvy departmental business users who create ad hoc reports on behalf of their colleagues.

3. Analytical modelers. Business analysts who create statistical and data mining models that quantify relationships and can be used to predict future behavior or conditions.

4. IT report developers. IT developers, analysts, or administrators who create complex reports and train and support super users.

According to our survey, most organizations have all four types of power users, although only 51% have analytical modelers.

 

 

BUSINESS ANALYSTS. Business analysts sit at the intersection of data, process, and strategy, and they play a significant role in helping the business solve problems, devise plans, and exploit opportunities. Their titles include “business analyst,” “financial analyst,” “marketing specialist,” and “operations research analyst.” Executives view them as critical advisors who keep them grounded in reality (data) and help them bolster arguments for courses of action.

Business analysts perform three major tasks:
1. Gather data. Analysts explore the characteristics of various data sets, extract desired data, and transform the extracted data into a standard format for analysis.

2. Analyze data. Analysts examine data sets in an iterative fashion—essentially “playing with the data”—to identify trends or root causes. Analysts will visualize, aggregate, filter, sort, rank,
calculate, drill, pivot, model, and add or delete columns, among other things.

3. Present data. Analysts deliver the results of their analysis to others in a standard format, such as a report, presentation, spreadsheet, PDF document, or dashboard.

Today, business analysts spend an inordinate amount of time on steps 1 and 3 and not enough time on step 2, which is what they were hired to do. Unfortunately, due to the sorry state of data in most organizations, they have become human data warehouses. TDWI estimates that business analysts spend an average of two days every week gathering and formatting data instead of analyzing it, costing organizations an average of $780,000 a year.

According some survey, most business analysts use spreadsheets to access, analyze, and present data, followed by BI reporting and analysis tools. However, in most cases, the analysts use BI tools as glorified extract tools to grab data warehouse data and dump it into a spreadsheet or desktop database, where they normalize the data and then analyze it. The next most popular tool is SQL, which analysts use to access operational and other sources so they can dump the data into spreadsheets or desktop databases (which rank number five on the list, following OLAP tools).

 

 

To improve the productivity and effectiveness of business analysts, organizations should continue to expand the breadth and depth of their data warehouses, which will reduce the number of data sources that analysts need to access directly. They should also equip analysts with better analytical tools that operate the way they do. These types of tools include speed-of-thought analysis (i.e., subsecond responses to all actions) and better visualizations to spot outliers and trends more quickly.