SQL Server Analysis Services Role-Based Security: Mid-Level Overview
A Mid-Level Overview of the Analysis Services OLAP Security Model:
Context: As we continue to explore improved business intelligence data architecture, with our overall mantra of "...fewer, faster, more sophisticated cubes providing more simultaneous users with answers to more questions..." (...gasp!), we need to consider role-based security. It's all good news, and here is the story...
In general: Role-based security explicitly grants or denies Windows Users or AD groups access to AS Objects. AS Role members can be either AD Groups or Windows Users. AS Security granularity can be just as granular and just as multi-dimensional as the AS cube itself, such that a specific restriction does not prevent full drill-down access to other needed information slices. For a BI front-end integrated with SharePoint, SharePoint Security might simply grant/deny a Windows AD group the visible rendering of a dashboard page, tab within a dashboard page, or a specific report. This is true in San Diego, as elsewhere (shameless SEO plug).
Analysis Services' Server Role: AS has just one Server Role, called ‘Administrator’. Members of this role can see/do virtually everything with that AS server instance.
Analysis Services Database Roles: Created by aforementioned AS Server Role, ‘Administrator’, they can receive admin (eg. ‘Create’,‘Process’) or user permissions such as read cube, read dimension, read cell (most granular), ‘read/write’ (rarely needed) or read data source (needed if end-user needs drill-through-action from cube to a more granular DW RDBMS). Frequently, multiple AS Database User Roles are created to assigned fine-grained security to AD Groups / Users. Lastly, if multiple cubes are deployed into production within a single AS Database (the container of the cube and related metada/MDX), the AS Server Role (Admin) has option to set AS Database Roles at either DB or (if security differs among these cubes) at cube level.
Note on Role ‘Additivity’: For denies vs. grants on same level/granularity of a dimension hierarchy, ‘grant’ wins over ‘deny’. However, when conflicting grant vs. deny exist on different granularities, finer grained wins whether an explicit ‘deny’ or ‘grant’. So, if required, Windows users with varied membership in a collection of AD Groups (example: ‘All SVPs’, ‘All Directors’ ‘Sales Dept’, ‘Operations Dept’), which are then assigned as members to various AS DB Roles, can be used in conjunction with AS’s aforementioned ‘additive’ grant vs. deny capability on different levels of dimensional granularity. A mid-level explanation of this follows…
AS Security Levels:
Highest Level – Dimension Security: An AS User DB Role can be explicitly granted/denied access to one or more dimensions in their entirety. A user denied access to the entire ‘Staff’ dimension, for example, would only be able to see that dimensions default value, which is normally ‘All’. This means that he will see all detailed cube data, except that he could not slice (filter), drill-down (group by or sort) by staff (employees, teams, managers, directors, etc).
Mid-Level - Dimension Data Security: As AS User DB Role may be granted access to the Staff Dimension per se, but denied access to peer-level details outside of his/her team, seeing only aggregates either laterally (other teams) and higher up (management).
* (Perhaps obviously,) an AS User DB Role may have this level of security configured independently across multiple dimensions.
* This level of security does NOT deny any specific facts/measures, only the dimensional criteria by which they are sliced.
Examples: One AS User DB Role has been…
Granted Unrestricted Permissions: Therefore, San Diego users see…
Sales Territory: North America Europe Asia Total
Reseller Sales Revenue: $75,000 $15,000 $25,000 $115,000,000
Reseller Order Count: 350 50 100 500
__________________________________
Denied Dimension Permissions on ‘Sales Territory’ Dimension: Therefore, San Diego users see…
Sales Territory: North America Europe Asia Total
Reseller Sales Revenue: - - - $115,000,000
Reseller Order Count: - - - 500
__________________________________
Denied Dimension Data Permissions on ‘Europe’ and ‘Asia’: Therefore, San Diego users see…
Sales Territory: North America Europe Asia Total
Reseller Sales Revenue: $75,000 - - $115,000,000
Reseller Order Count: 350 - - 500
__________________________________
Fine Grained Level - Cell Security: An AS User DB Role can be granted (or denied) permission to view one cube measure (one fact table column), but not another. For example, in a ‘Student Retention’ cube, one AS User DB Role may be granted read permission on the ‘StudentInAttendance’ measure, but neither the ‘StudentBillable’ or ‘StudentAtRisk’ measures.
* If no ‘Dimension Data Security’ restrictions exist for this AS User DB Role, then the above scenario applies to all dimensions as they slice the measures.
Denied Cell Permissions on ‘Reseller Sales Revenue’ measure: Therefore, San Diego users see…
Sales Territory: North America Europe Asia Total
Reseller Sales Revenue: - - - -
Reseller Order Count: 350 50 100 500
__________________________________
Finest Grained level – Combined Cell Security and Dimension Data Security: Combining these two approaches, an AS User DB Role can be granted or denied access to any combination of measures, dimensions (dimension tables), dimension attributes (dim table columns), and dimension members (rows/records in any dim table columns).
Combined Cell Security and Dimension Data Security: Therefore, San Diego users see (extreme example)…
Sales Territory: North America Europe Asia Total
Reseller Sales Revenue: $75,000 - - $115,000,000
Reseller Order Count: - 50 - 500
__________________________________
As a departing thought... keep in mind that none of the above permission restrictions prevent an AS User DB Role from performing fine-grain-detailed drill-down on other needed dimensions (eg. Student, SOE, University Modality, Geography, Date, etc.) or other unrestricted measures (facts).
When other such dimensions are added as rows or columns (ie. cross-joined) in the above examples, the resulting values will provide all details, except wherein the above permissions were explicitly controlled. The takeaway here may be that BI teams can not only build, but also carefully secure fewer, more sophisticated cubes with highly granular security architecture, rather than re-building otherwise-identical cubes simply in order to provide adequate security. Slick!
Daniel Upton
Business Intelligence in San Diego
Business Intelligence Team Roles: Data Modeling in San Diego
The Role of The Data Modeler on the BI Project Team — Although Business Intelligence projects usually require significant participation by non-IT business stakeholders for such roles as requirements analysis, user acceptance and, lest we forget, sponsorship and budgets, the bulk of daily BI work, for better or worse, remains largely technical, thus requiring database and software technology pros as core participants. Duh!
Obvious, right? Yes, and this rule applies in San Diego as elsewhere, but let's consider some real-world business intelligence situations from both the technical and business perspectives and see what challenges the hefty influence of technologists presents. Technical: Many technical people in data warehousing and business intelligence directly observe how projects suffer from a lack of sustained, helpful participation by the business side. Business: Supervisors of business-side BI project team members will, justifiably, find other tasking for their staff who return from such meetings talking more more about technology — (whether it be cool (Web 3.0-style collaberation) or un-cool (firewall port exception #'s)) — more than they talk about how the team is preparing to deliver hard data support for specific business decisions. Technology: Murphy's Law - At the next project team meeting, which was supposed to include a discussion on business requirements gathering processes between the database dev lead and the requirements analyst, few are surprised that the requirements analyst is double-booked and misses out.
Business/Technology Executives: After the next executive briefing, IT management issues a terse, "hurry up and expedite a prototype" order, and away we go. To we're now driving big nails (think 'ETL' here) without a blueprint.
Business Intelligence Project Risk: Development without Architecture — By now, the team must gloss over one of the most important milestones prior to development, a well-conceived dimensional data model. Instead, the tech lead, who is rarely a lover of formal processes or documentation, will tend to conceive of a model that requires minimal transformation from source data, and from which only he and other database engineers understand how it will need to be manipulated into satisfying actual business requirements. In fact, many OLTP database pros view their OLTP database as "the business", such that any transformations to it equate to a distortion. Business Intelligence pros like myself, however, tend to agree that OLTP databases transact the business, while transformed dimensional- and OLAP-databases provide the most insight into it. Even if a hastily transformed, or non-transformed, data model can be tortured into satisfying the documented requirement (which we've already identified as being insufficient), it is unlikely that it is flexible enough to extend itself to satisfy complimentary analytic needs. In truth, no amount of text documentation and end-user mock-ups can express a multi-dimensional architecture in a way that a variety of stakeholders can appreciate. So, what is the solution?
The Solution is a Smart Dimensional Data Model — one that has makes it visually obvious how most expected queries will be resolved. The dimensional data model serves as the 'stake in the ground' not only for much of the business logic that will be delivered to the business, but also serves as the guiding document for the big work of ETL. In business intelligence, ETL that begins without a destination schema is, in my not-so-humble opinion, a sin! A solid dimensional data model informs ETL engineers of the essentials of required data granularity, hierarchic relationships and field naming. It also makes clear what data quality tasks are priorities. Why did I refer above to 'most expected queries' and not all? If OLAP cubes are included, then the cube itself will likely contain MDX-based calculated metrics or key performance indicators (KPIs), downstream of the dimensional model. In that light, although the data model will not display those metrics directly, it still needs to surface the exact fields from which they will be calculated.
Data Modelers to the Rescue — An excellent data modeler — who combines expertise in dimensional design, interpersonal communications relevent business domain knowledge and, as applicable, expertise with OLAP cubes — is, in fact, the BI Project Team's key bridge-player to the business. If a business specification leaves dimensional modelling questions un-answered, the data modeler must insist on the spec's revision. Subsequently, if the data modeler cannot convincingly demonstrate to the business requirements analyst exactly how the dimensional model satisfies business query requirements, then the model needs revision. When both of the above conditions are satisfied, the project is ready for the big work of ETL — work which is now informed by a strong destination data model which will provide inherent answers to the many ETL issues that will arise.
The Data Modeler's Role in Relation to System Architects, ETL Architects, Data Warehouse Architects, Business Intelligence Architects, OLAP Developers, Dashboard Developers and Managers — Far from a being a handful of best-case talking points, dimensional data modeling is critical to BI success and thus requiries an owner. If a skilled data modeler also demonstrates business domain data expertise and, as applicable, principals of OLAP, data visualization/dashboards and reporting, he can fill the role of Business Intelligence Architect alongside an experienced senior engineer who will oversee hardware, system architecture and ETL. If, from the technology end, a senior database engineer understands everything up to, but necessarily including, dimensional data models, he can serve as data warehouse architect alongside less experienced modelers and developers of OLAP cubes, dashboards and reports. Lastly, on all BI Project teams, and especially smaller teams, any role with the word "Architect" in it implies a degree of leadership not only in design, but also technical implementation.
If, as often happens, most of this responsibility falls on the multi-tasked database manager, then their bandwidth to truly own the data modeling process is very limited, especially in the context of their competing concerns for expediency and the myriad fire-extinguishing tasks.
Real World Business Intelligence Roles — Do most DW/BI projects include a dedicated system architect, ETL architect, data warehouse architect, and business intelligence architect? Unless it's a NASA mission, I doubt it — there would be no room left at the meeting room table for business analysts, who specifiy the requirement or developers who build the solution.
Bonus Topic...
Dimensional Data Modeling in the Age of QlickView and Gemini / PowerPivot — These new 'in-memory' quasi-cube-generation platforms, which empower non-technical business analysts to rapidly build their own ad-hoc BI solutions, are indeed a paradigm shift in business intelligence. How will they effect the important of data modeling? My take is that, sooner or later, they will only increase the importance of standardized, conformed data modeling, serving as the organization's last chance to offer up standardized data to business units and in return, receive the covetted "single version of the truth". Since the source data will be directly accessible to the newly-empowered analyst, its warts will be harder to conceal. So, whether the tasking is to design good data models from the get-go or to make re-do bad models later, I think data modelers will continue to stay busy. But, OLAP people who don't yet know modelling beter hit the books.
Daniel Upton
Business Intelligence in San Diego
How-to: ‘Using MDX to Enhance PerformancePoint Data Visualization’
Details: In the above document, I show that a few simple MDX techniques in BIDS/SSAS and PPS DD can go a long way. I use...
(1) Simple MDX expressions: Create a number of ratios for key business metrics (eg. ‘profit margin %’) to be browsed alongside a base cube measure such as ‘profit $’.
(2) Simple MDX named (measure) sets: Create in BIDS/SSAS Calculations tab, and include newly-created MDX ratio expressions in set list ABOVE the non-ratio measures
(3) SSAS Time Series Utility Dimension (not so simple, so the code is provided in the document): In addition to popular Time-Utility Dimension expressions such as ‘YTD, Prev Yr, and Rolling X Month Avg’, I’ve added ‘Rolling X Month Avg + 2 Std Dev’ and the same for ‘… - 2 Std Dev’, so that users can perform self-service trend analysis on all cube measures. For a PPS Analytic Chart visualization that’s easy on the eye, of course, I recommend the line chart as default.
Have a look!
Enhancing Data Visualizations with Multi-Dimensional Expressions (MDX)
(1.) For significantly "dimensional" metrics (those who's variations in values must be known in relation to dimensions (eg. time, product, customer, geography, organization, vendor, promotion, etc.), MDX is by far the best place to crunch them, so that upstream data marts, even cubes themselves, need not store largely redundant calculated values.
(2) In MDX-friendly dashboards, such as Tableau and PerformancePoint (demonstrated in presentation), the use of MDX Named Sets (specifically, sets of quantitative measures) can add significant flexibility to chart (and/or tabular) data visualizations, since the "Name" in an MDX Named Set can be called as a single Reportable field, just like an individual cube measure can be. This new reportable field is then used in conjunction with an identical MDX-based filter, such that users can effectively change the fundamental content of chart or tabular output. Self-serve business intelligence, anyone?
(3) Although MS PerformancePoint 2007 Dashboard Designer, a Version 1, provides limited flexibility in it's Analytics Chart Report, better visualizations can be made via MDX "Percent" metrics, which can then be added BEFORE comparable non-percent measures, into the standard-bar Analytic Chart, such that, wham!, the chart is suddenly mult-axis, with the left Y-axis showing non-percent values coinciding with bars (eg. gross revenue $, gross profit $, and a right Y-axis showing percents (eg. gross profit margin percent) to coincide with a percent line. A neat little improvement over an otherwise "wooden" bar chart.
(4) Some of the most common business metrics involve time-series and apply to many quantitative measures: Examples include year-to-date, same period last year, rolling averages and periodic growth/shrinkage. MDX is a nearly ideal environment to code, even to standardize these time-series metrics, for the organization. For a reasonably detailed "how to", including MDX script, please see the download.
(5) In a nutshell, the basic value proposition of an MDX Utility Dimension is straightforward and significant:
(a) Standardize routine metrics for easy, consistent re-use in all OLAP cube applications.
(b) Reduce lines of code, thus code errors, by expressing metrics WITHOUT specific associated measures, so that we don't have to re-write the code five time if a metrics applies to the natural aggregations of 5 measures. Note MDX Scope statements are needed to wall-off those measures for which the utility dimension metrics do not apply.
(c) Comparison of MDX Utility Time Dimension to Analysis Services' out-of-box 'Time Intelligence' cube algorithm: Time Intelligence is limiting insofar as it requires the developer to specify just one specific Time Dimension hierarchy to which the metrics apply, and then entirely re-code the MDX Metrics for each additional hierarchy in that same dimension. Lot's of mostly duplicate code. Secondly, Time Intelligence specifies applicable measures inside each MDX metric. Takeaway: When revisiting the system-scripted Time Intelligence metrics for enhancement, you find code that is less compact and harder to cleanly extend with new metrics (eg. the boss now wants "12 Month Avgs +/- 1 Std Deviations", not just "3 Mos Avg +/- 2 Std Devs". In Summary: To me, the Time Utility Dimension seems like cleaner, more extendable code.
With that topic done, and with me being sold on the concept of MDX Utility Dimensions, I will soon be investigating other implementations of MDX Utility Dimensions, based on feedback in the following LinkedIn Discussion Thread (while it lasts) is: http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&gid=127230&commentID=6614305&discussionID=1249071&goback=%2Eanh_127230&trk=NUS_DISC_Q-subject#commentID_6614305
MDX people: Join the group and the discussion!
Google PowerMeter, a new gadget powered by San Diego Gas & Electric
Built on the new Smart Meter foundation, SDG&E customers — virtually all San Diego County residents — will soon be enjoying yet another consumer innovation. SDG&E has just announed a partnership with Google. To understand it, first step back for a moment. SDG&E is the nation's first utility to mass-deploy Smart Meters. In a nutshell, Smart Meters communicate , using wireless and cell-phone technology with SDG&E headquarters, so that, among other benefits, meter-reader people will no longer need to visit our residences just for routine readings. Smart Meters are currently being mass-deployed in Escondido, CA and the deployment will continue through 2001, when all San Diego County meters will be Smart Meters. For more on Smart Meters, click on... http://www.sdge.com/smartmeter/
Anyway, today, SDG&E announced this new partnership with Google. Once again, in a nutshell, residents who want more choice, control and convenience in how they get their information about energy use can use the new "Google Power Meter" gadget and with a few clicks of the mouse, create a personalized iGoogle website. For more on Google Power Meter, click on...http://www.google.org/powermeter/
What was my little role? Since January, I led a team of utility analysts, developers and testers in building the "Deployment and Operations Reporting System" for Smart Meter. It was fun, and I'll miss my co-workers. Hopefully, I'll get a chance to assist SDG&E again in the near future. Until then, I'm off to fine-tune a Business Intelligence data infrastructure for a leading golf manufacturer. It's all good!
Essential Tech Reading: Review of "Rational Guide to Planning with ...PerformancePoint Server" by Downs and Barclay
Adrian Downes and Nick Barclay have written another excellent, even essential, PerformancePoint Server learning guide. If you've already completed "Rational Guide to Monitoring and Analytics with ...PerformancePoint" and want to quickly learn how to add the paradigm-shifting planning, budgeting and forecasting capabilities available from PPS Planning, check out my recent review of "The Rational Guide to Planning with... PerformancePoint Server 2007" by same authors, which deserves your consideration as a learning guide. Here's the link...
http://www.amazon.com/review/R2JKFWVZGGF0UJ/ref=cm_cr_rdp_perm
Performance Management, Business Intelligence and The Role of the New Performance Dashboard
INTRODUCTION
Business intelligence and performance management are related disciplines and technologies that, in combination, empower us to extract maximum insight from integrated data, use that insight to inform better business decisions, take appropriate direct action based on those decisions and, in fact, do all of the above as a matter of our normal work routine.
EXISTING STATE OF BUSINESS INTELLIGENCE
Business Intelligence may be aptly defined as a state – enabled by an optimum data architecture accessible via specific software – wherein business decision makers routinely enjoy fast, direct, user-friendly, continuous and role-appropriate access to a single version of the truth about actual business results, and their relation to standardized metrics, as a foundation for good business decisions. Activities for business intelligence users generally include monitoring data, analyzing it in detail or obtaining on-demand reports. Frequently, business intelligence data is organized multidimensionally — the way we naturally think — according to relevant criteria (dimensions) like people, things, products, geography and time.
In that regard, business intelligence represents close to an ideal state of knowledge capture from data. However, business intelligence applications usually do not, within their own sphere of functionality, support the initiation of any actions that may be taken based on decisions made from new insights.
“Performance Management” (PM), which is evolving from a combination of business intelligence and corporate financial planning disciplines, is defined by Wayne Eckerson of The Data Warehouse Institute as… “a series of organizational processes and applications designed to optimize the execution of business strategy”. “Execution” is a key word here and, in performance management, execution typically involves forecasting, planning and budgeting.
INSIGHTFUL ACTION: THE IDEAL STATE
Architecture and applications that blend Business Intelligence and Performance Management features can add high value by allowing now-enlightened decision makers to routinely take appropriate action on those decisions using automated, standardized, and collaborative workflows for contributing to, reviewing or approving the specific execution of forecasting, planning and budgeting activities, thereby “closing the loop” and initiating intended changes based on insight supported by hard facts. Budgeting, in particular, is the activity in most organizations, where plans resolve into changes in resources, tasking and goals.
TWO CHALLENGES
In practice, we often find that not all of the data needed for the desired analyses ever finds its way into a business intelligence application. On the one hand, some data — which will provide the required real-time key performance indicators for performance management, may not arrive in the business intelligence application until its too late to be valuable. An example of this might be an airline's real-time data each day about late departures. Another reason for this is more generalized: Because of spreadsheets’ (a) continued popularity, (b) ease of use, (c) robust analytic capabilities, and (d) ease of sharing, some high value corporate data continues to remain in spreadsheets, with their traditional weaknesses in terms of version control, accuracy and security. In the past, this reality has amounted to a major compromise to BI’s promise of “a single version of the truth.”
On the performance management side, although some tasks, such as forecasting and "what if" analyses, are best performed using the available analytic muscle of a business intelligence application, other processes, especially more procedural processes like task assignments, progress notifications, draft submittals, reviews, edits, re-work and approvals, while perhaps based on that same business intelligence data, are inherently very different activities and should normally be performed using the most obvious tools. As is the case with business intelligence, the popular medium for these processes has been the informal, arcane combination of e-mails and, yes, more spreadsheets, with the resulting convenience, as well as the potential for unpredictability and error that this combination has traditionally offered.
ENTER PERFORMANCE DASHBOARDS
Performance dashboards are like other dashboards in that they provide busy people with relevent information at a glance. What makes performance dashboards unique is that they deliver business intelligence and/or performance management information and support the related workflows. A good performance dashboard will…
* Provide consumers with an appropriate combination of "at a glance" key performance indicators, scorecards, pivot tables and charts, as well as some in-depth, drill-down reports and advanced data visualizations,.
* Be virtually effortless to deploy to user workstations.
* Flawlessly orchestrate the integration of a wide variety of dissimilar data sources, including OLAP cubes, data warehouse tables, line-of-business databases (for real-time performance data), flat files, web-server lists, survey data, spreadsheets and other format, in order to serve as a final, crucial data integration point prior to user views. All needed data sources should be able to be seamlessly integrated as data sources, so that the end user experiences consistent multi-dimensionality and, of course, accuracy. As an example of flawless orchestration, various examples of time series information (eg. year-to-date, year-over-year, etc) that originate in dissimilar data sources must integrate transparently.
* Coexist with and, in fact, integrate closely with and orchestrate e-mails as the preferred vehicle for notifications related to performance management assignments, progress notifications, reviews and approvals.
* Support the actual performance management analysis work by managing the storage, security, role-based access, and version control over spreadsheet entries for forecasts, what-if analyses and, most importantly, the budgets themselves.
* Orchestrate these spreadsheets’ consumption of data from — and secured write-back of new data to — the business intelligence application / data warehouse, such that what-if analyses, forecasts and proposed budgets comfortably exist alongside actual results data in the data warehouse and/or OLAP without danger of losing actual data to inappropriate overwrites.
Performance dashboards that can do all of the above, especially the controlled orchestration of disparate sources and two-way data flows between the data warehouse and spreadsheets, are truly next-generation solutions that will assist in freeing up information workers to overcome organizational paralysis and close the insight-decision-action loop on a routine basis.
NEXT IN SERIES:
* Microsoft Office PerformancePoint Server 2007
* Detailed Feature Comparisons in Leading Performance Dashboards
Praise for "Rational Guide to M&A with ...PerformancePoint Server" by Barclay and Downes
http://www.amazon.com/review/R1RFLK2PF6PLT3/ref=cm_cr_rdp_perm
"KPI Street Cred" ...You heard it here first!
Why should you care about who "owns" the KPI's in your organization? If you're into Business Intelligence, you probably care how well the KPI's are received and respected. From a technology perspective, the issue of ownership also says a lot about how the KPI's should be managed and where authoritatively stored, perhaps in the context of a Business Intelligence or Performance Management application By "ownership", by the way, I mean a sufficient amount of the actual, or at least perceived, control over something, without which a person may not feel fully responsible when things go well, and will almost certainly try to avoid blame when things don't. On that note, let's consider the pros and cons of two opposing perspectives. If you are involved in BI, you will care!
Technology-Centric Perspective:
Although KPI "target result values" should be delivered to the technology side by non-IT stakeholders, the KPI rules themselves should be embedded within the BI application, so that any application pulling data from it can enjoy the KPIs. A common way to do this is to use use scheduled data extraction, transformation and loading processes to merge the various target result values from their own data source into fields alongside the actuals and then create KPI's within the OLAP cube environment. One unfortunate business result of this approach is that the "ownership", or at a minimum, the perceived ownership, of the each individual KPI has now essentially been split at least three ways - between the business stakeholders, the ETL developer and the OLAP application developer (report developer, etc.) As such, not any one of the 3+ parties (for each individual KPI) truly perceives that she owns it, and each can point the finger at others when results are questioned. Such questioning, whether truly justified or not, tends to seriously limit credibility in business units.
"KPI Street Cred" anyone? ...You heard it here first!
Anyhow, one big advantage of this approach is that KPI's are now available to all users and applications downstream from the OLAP cube. Another might be that, in a specific organization, perhaps due to lack of available time or buy-in on the business side, nobody exists to take ownership away from IT.
Business-Centric Perspective:
Although, admittedly, actual business results should be stored in the data warehouse and/or OLAP cube, the KPI rules themselves should be wholly owned, and controlled by, by the respective business stakeholders, because their people will be evaluated or compensated with respect to how well their actual performance stacks up to targets. So, the KPI's usage in a Business Intelligence or Performance Management application must not obscure this ownership. To support this approach, we would have all of the KPI target metrics stored in a single, standardized, well-secured spreadsheet, controlled by a sufficiently objective, high-level authority on the business side. This spreadsheet would include all metrics for each KPI, including type ("increasing is better vs. decreasing is better" vs "closest to target is better"), theoretical maximums (where applicable), minimums, any rules about appropriate visual image (stoplights, arrows), and how a child KPI mathematically roles up, in relation to it's sibliings, to a parent KPI.
If we agree so far that this perspective improves the "ownership" issue, we can now ask ourselves, "What must occur — in addition to the non-trivial matter of serious buy-in from the business side — in order to make this business-side spreadsheet approach, which is justifiably scary to IT people, successful?" We will need a Performance Dashboard development environment, like MS PerformancePoint Server's (PPS) Dashboard Designer, with two characteristics: First off, for each KPI it must have the sophistication to accurately, consistently merge actuals results from the data warehouse and/or cube with target results from the KPI spreadsheet. Secondly, in keeping with the business-side ownership philosophy, it should be sufficiently user-friendly, as is PerformancePoint, so that specific analysts working for the business-side (not just BI developers) can, with minimal training, do the actual design work of completing the design of the KPIs themselves and perhaps also placing them into scorecards in the dashboard, too. Any downside to this approach? Perhaps only that this represents an increased comitment to the Performance Dashboard platform as the source of merged KPIs, rather than the cube itself. However, perhaps the greatest challenge is the cultural/process shift wherein the business-side now controls data that directly feeds into an enterprise application.
Being a bit of a paradigm changer myself, I'm in favor of the Business Perspective, and I encourage IT people to consider the benefit when the business side owns more of their own data.
What are your thoughts?
Getting BI Skills... To Land The Job... To Build On Those Skills... To Advance On The Job... To...
During a presentation I gave on SQL Analysis Services 2005 in January 2007 to the San Diego SQL Users Group ( www.sdsqlug.org ), one attendee asked me for my opinion on how to go about acquiring BI skills. I'm no guru, but as one who is a ways along the skills continuum, here is my take on just one aspect of this question, which is...
First Off, Exactly What Are The Core BI Technical Skills?
(1) Relational Database and SQL Language Fundamentals:
Although BI is certainly a departure from traditional / transactional RDBMS principles (eg. third normal form), it is still significantly based on, or at least built from, relational DB management systems' concepts. Additionally, most BI applications are built around large data sets, for which solid SQL language skills are essential. Add these two realities together and most will agree that BI is a place for interested database novices to progress towards, but not to start with. On the other hand, business people may continue growing their BI Analysis skills without necessarily spending much time in this topical area.
(2) Dimensional Modeling:
Designing the data warehouse (DW) or data mart (DM) is, I think, the theoretical foundation of BI. Once the business process of interest has been defined, we define the facts (aka measures), which are the (usually numeric) values to be analyzed according to dimensional criteria (to be defined shortly). Examples of facts in a Sales data mart may include quantity of units sold, unit sales price, and unit cost. We then define dimensions and their attributes. To extend the example, a Sales data mart may include a product dimension with attributes of Product ID, Product Name, as well as hierarchic values such as Product Sub-Category and Product Category. A third dimension may be the Date dimension, which may include analytically intuitive attributes such as Date, Day of Week, Holiday, Month, Quarter and Year. Importantly, although an overriding goal of dimensional modeling is to establish a highly-intuitive, refreshingly simple design, the potential potholes and bends in the road are many, and thus Dimensional Modeling requires study. Fortunately, it's fun. Dimensional modeling calls for, and rewards, experience with not only database design but the business subject area itself.
(3) Extraction, Transformation and Loading (ETL):
Once the DW's dimensional design is complete, the hard work of gathering data from multiple sources, cleansing it and transforming it's data types, granularities and/or aggregations, loading it into the DW, and the scheduling much of that to iterate, begins. Many consider ETL to be the 800 Pound gorilla in the DW project planning room. It's specialized work performed by experts, it's difficulty may be under-appreciated by key business-side stakeholders who wonder "How hard could it be...?", and the likelihood of it's on-time, on-budget successful delivery is obviously vulnerable to even subtle changes in project requirements. In my opinion, ETL may be one role in the team cannot afford for the individual to just learn it as she goes.
Although they are often considered as an advanced database skill set, people with the desire and the dedication to master this specialty, will enjoy a strong demand for their work.
(4) Analytics / OLAP Cube Development:
From my viewpoint, this is the most fun part. The only part more fun is showing the analyst / BI user how to go about "finding the money" and then being around for the high fives when she finds really big bucks.
In building a high-functioning OLAP cube on top of the DW as it's data source, we are creating a truly multi-dimensional on-screen data browsing structure with fast (ideally "speed of thought" query performance, rich semantics, calculated fields, key performance indicators (KPI's), pre-calculated aggregates, a realistic variety of hierarchic default drill-paths and a strong foundation for the inevitable collection of hard-copy reports. MS Excel is, of course, a popular OLAP front-end browser. With a sufficient budget, the OLAP application will also benefit greatly from a rich-featured, browser-based, zero-client-footprint, multi-user front-end OLAP web server (that was a mouthful), such as is provided by Applix, Business Objects, Cognos, Hyperion, MicroStrategy, Panorama, ProClarity, Strategy Companion, SAS and others. The user-browsing capabilities of some of these platforms is extensive, and so the work of configuration, customization, user-training and administration with these platforms is no trivial matter.
For analytical business-specialist types like myself, the analytics / OLAP development phase is uniquely exciting insofar as it delves deep into the actual analyses that will be performed and enables them for users.
(5) Data Mining:
I will only deal with this advanced area briefly. Data mining starts, in a sense, where OLAP stops. Whereas OLAP enables analyses according to known factors (dimensions) in order to determine "what did happen and why?", data mining allows the system to detect patterns from "test data", and then apply those patterns to new data sets and thereby furnish potentially insightful answers to questions no user thought to ask. In that context, data mining is often used for predictive analyses of "...in light of all of our data... what will happen?"
Data Mining practitioners, whose expertise is usually built atop a foundation of statistical analysis skills, may often be relatively uninvolved in database engineering per se, but rather are advanced power-user specialists who can generate unprecedented insight from a database. As such, data mining is among the hottest of hot database skills.
(6) Report Development:
Although the aforementioned OLAP on-screen analytics may tend to reduce the sheer number of semi-redundant reports that IT is asked to generate, is does not signal the end of the need for reports per se. Reports retain the role as the hard evidence of information queried and, hopefully, valuable knowledge gained. In my opinion, a reasonably small collection of carefully conceived, uniquely insightful OLAP-based reports, many of which should include an extensive set of user input parameters, are crucial BI assets. Ideally, OLAP-browsing analysts should be able to drill directly and seamlessly from on-screen browsing into a variety of these on-screen reports with their input parameters automatically pre-selected according to the users currently OLAP browsing slice-and-dice positioning. As the analyst successively "finds the money", she should be able not only to bookmark those OLAP browsing locations, but also document each of them with appropriately filtered hard-copy reports. Lastly, these report hard-copies must include automatic documentation (ie. in the report footer) of all input parameters settings, so that the report can be duplicated later.
Personally, I view report development skills as an excellent compliment to RDBMS fundamentals and SQL language skills for novices interested in BI.
(7) Project Lifecycle Process Methodologies:
These competencies, which span skills that are technical, analytical, organizational and interpersonal, may be the single most crucial factor in BI success in light of the sheer complexity of BI development. For an article on the mainstream "Data Warehouse Lifecycle, click on... http://www.businessintelligence.com/ex/asp/code.147/xe/article.htm
For an intriguing book on the "Business Intelligence Lifecycle", click on...
http://www.amazon.com/gp/product/0201784203/ref=wl_it_dp/104-5823156-6544767?ie=UTF8&coliid=I3JFAKDAO7BDG0&colid=266K0ULFTUTZY. Of, if that link breaks, visit Amazon.com and search for the book entitled "Business Intelligence Roadmap" (ISBN-10 0201784203), by Moss and Atre. What's intriguing? They tout Extreme Programming (XP) as an effective BI development process. Although I have not been exposed to XP usage in BI, I must concede that anything that can reliably accelerate progress amid a high degree of complexity needs careful consideration.
In my view, expertise with software lifecycle methods simply comes with experience. Having said that, the topic makes for enlightening, and occasionally enjoyable, reading.
Let's close this blog entry with some open questions...
Question #1: For BI newcomers from a variety of backgrounds, which of the above skills are reasonable to seek and in perhaps what sequence from a career-development standpoint?
Question #2: Under what circumstances can any, or many, of the above roles be wisely placed on a single individual, and what risks do these choices create?
Question #3: To what extent is Agile or Extreme Programming (XP) an appropriate methodology for BI?
What are your thoughts?
...