Tuesday, July 14, 2020

Measuring Culture, Diversity and Inclusion.

Today Diversity and Inclusion is a big topic in most organisations, I was reading a great article by Ubisoft on this and it got me thinking. How do we measure this and will our measurements reflect what is truly happening.

As I am from South Africa we called D&I transformation and there was a lot of things that in the 10 years I was working in South Africa that I have learned. But one thing always got me in all of this was was the question correct and are we tracking the right things. In my point of view there is 5 points that should be considered to address D&I:

VALUES

For me one of the key metrics and understanding about people are their value system tree. This is a key measure to see how a team will interact and also a company as a whole. This is not the values that the company promote but the actual values that every person have, what they see as important. There are in most psychology papers four (4) Categories of a Personal Value System

  • Personal Values - Personal values are those traits we see as worth aspiring to, and that define our character.
  • Spiritual Values - The values that connect us to a higher power and give us a sense of purpose beyond our material existence.
  • Family Values - To love and care for those we are close to; our children, our parents, other family members, and our friends.
  • Career Values - The best use and expression of our talents and skills for the purposes of contributing to society and for monetary compensation.
These value systems can be categorized along multiple axes:
  • They can be personal, held by an individual and applicable only to an individual, or they can be communal or societal, defined by and applying to a community or society. Communal value systems may be legal codes or take on the force of law in many societies.
  • They can be internally consistent, where the broader ideological values derive logically as natural consequences of the particulars of fundamental ethical values, and where values do not contradict each other, or they can be inconsistent. Although ideally a value system ought to be consistent, quite often this is not the case in practice. Note that valuing the consistency of a value system is itself a sort of 'meta-value' that could be present or absent in a given value system.
  • They can be idealized value systems (ideal representations of an individual's or group's value prioritization) or realized value systems (how such a value system is manifested in reality, in the actions and decisions of the individual or group). Idealized value systems tend to be absolute, in that they are codified as a strict set of proscriptions on behavior, while realized value systems contain conditional exceptions that are rules to resolve collisions between values in practical circumstances.
According to the Spiral Dynamics model originated by Clare W. Graves, an alternative cultural view on value systems is that they are evolutionary. As such, the dominant value systems in a society depend on the existential problems with which the persons in that society are coping.

To illustrate what I mean the impact of values on teams and companies I did my own value test at https://www.psychologytoday.com/us/tests/personality/values-profile Free / $8.90 USD for full report and then also use https://personalvalu.es/ (https://personalvalu.es/personal-values-list) and the following was my results in short.

Your most important values are:
#1 ACCOUNTABILITY ( responsibility, dependability )
#2 FINANCIAL STABILITY (stable income, financial freedom)
#3 CREATIVITY (imagination, inventiveness)
#4 LOYALTY (faithfulness)
#5 HONESTY (sincerity, frankness)

From the psychology today my other values senses are:

Dominant Values — values you consider very important
  • Theoretical Values: People with theoretical values regard logical thought and the pursuit of knowledge very highly. They respect the value of education, both formal and informal, and believe in learning for the sake of learning. They don't make decisions based on a set ideology; rather, they try to base your opinions, beliefs, and decisions on "truth". Rather than listening and blindly following what others are saying, people with theoretical values make their own choices based on all the information available to them. This translates to a very deliberate, logical way of thinking. They want to understand how things in the world work, and are not afraid to ask why something is the way it is. 
  • Realistic Values
  • Aesthetic Values
Influencing Values — those that you consider moderately important
  • Social Values
  • Traditional Values
  • Political Values

Minor Values — values that you consider least important
None of the values fall into this category

Now from the above lets say my manager has the same aligned values and a college that is senior has values as follows:

#1 REPUTATION
#2 RESPECT
#3 CREATIVITY
#4 POPULARITY
#5 EQUALITY

I added equality as you will see in a moment what that means in practice. Because my leader and I have aligned values we will understand each other and will have less conflict, in this sense I will feel that my leader understands me and that I am included in the team. Also my leader will feel that I am loyal to him as this is a value we share. Now as for my co-worker what do you think will happen in team settings if we don't agree with them or show that they are wrong in how they think? Because reputation is a high value for them they will feel direct conflict in an open forum makes them look stupid and harms the popularity they have with people, while my values are taking accountability for my actions and thoughts I don't really care what people think about me that much as I like to search for the truth even if that means I am wrong or I might hurt some ones feelings. Also my co-worker might feel I don't respect him as I always in conflict debate with him as I might feel that because he does not debate with me he is not really respecting me as I like to seek the truth by talking. Now if we do a survey based on this the results might look bad as one feels part of the team while the other does not because none understands how they values are structured. This cause great tension in organisations. As if the values of the company does not align practically with people then we might feel the people in the organisation are not keeping with the values. As a organisational value might be "treat people with respect" for me if people does not take responsibility for their actions and I must hold them to it as they must hold me accountable for mine we do not treat people with respect. While my co-worker feels that conflict can damage reputation of the individual, same value, different implementation.

As for equality there are 2 schools... first equality of outcome and the second equality of opportunity. These implemented practically can cause lots of arguments with people yet they are the same values and if some does not have this as their high value list they might even understand how people want to be equal while everyone should be different and thus that makes people in practice unequal in perspective. Not everyone can be a master chef, even if they try and thus they are not equal to any master chefs, but they are painters and thus the master chef cannot compete with them there. Our job is to understand these values and track people alignment with them, the more values are aligned the more people feel included.


QUESTIONS

The next phase is the question that can be asked, this is what most companies do to see their D&I, but this is also where most companies fail in implementing change tracking. Like illustrated before without understanding values questions like "I feel I belong in my company" does not show any tracking capabilities as people with different values will not align. What if we use the questions:
  • I feel that I can practice my values everyday in my company?
  • I understand what my values and the company values are aligned with?
  • What of the following company values resonates the most with you?
Even questions like what statements align with the company values are more insightful than the question I feel I belong. Not everyone should feel they belong in companies and companies should inspire them to find a company that would make them feel they belong, this is where initial interviews are critical to see if an potential employee will be able to feel they belong. This is nothing to do with diversity but everything with inclusion. Diversity is a easy tracker, but alignment is very hard. And inclusion should be the first priority to get the right people in, then diversity becomes an easy conversation and also career aspirations. Also most of the questions are very vague and does leave too much room for misinterpretation. Like "the leaders of the company is doing a good job in..." what leaders? the top management that I never met? or the boss that works with me every day or even his manager? Everyone will answer differently in their state of mind an thus give bad or good feedback to the wrong tier of leadership.

This bring me to the last point in questions section.. the state of mind of the participant. If you don't take the emotional state of someone in these questions they might skew the results. If I had a bad day at home and someone said something at work that triggers me I might answer very negative the questions while if a week later everything is resolved and I feel better the answers might be a lot more positive. As a company make sure that you know the state of each and have follow-up questions to see if there was change in the same answer. Someone with depression might not feel they belong, this is nothing to do with the company or the team and everything with the person's state of mind. (People with depression should be helped, but it takes more energy from people to make them feel included at work as their life is impacted differently)

MEASUREMENTS / METRICS

If you cannot measure it you cannot change or track progress, after understanding the values and questions we need to understand the metrics that we will use to track and see if the measures will work.

Metric for diversity can be as follows:

  • Employees overall, by function, seniority and tenure (cut by demographics)
  • Employment status (i.e., full-time, part-time, contractor) (cut by demographics)
  • Management and leadership (cut by demographics)
  • Salary (cut by demographics) – Raises and bonuses (cut by demographics)
  • Board of directors (cut by demographics)
  • Candidate pools and hiring funnels, by role (cut by demographics)
  • Voluntary and involuntary attrition rates (cut by demographics)
  • Promotion rates (cut by demographics)
  • Formal and informal complaints (cut by demographics) – Complaint resolution status (cut by demographics)
For the inclusion the metrics are a bit harder to measure, but as discussed this is where the values and questions play very important part. An organisation’s diversity and inclusion metrics should serve three purposes: diagnose risk areas and opportunities, track the progress of initiatives, calculate return on investment. Some important metrics can be :
  • Retention: Comparing average tenure for employees from monitored groups to average tenure across the workforce or average tenure of members of the dominant group.
  • Recruitment: Comparing the number of applicants for open positions from monitored groups against the potential pool of applicants from monitored groups or labor market representation.
  • Employer brand: Compare the quality and strength of your employer brand among different identity groups.
The important factor for metrics is to Establish baseline measures. It is impossible to track progress unless you have a baseline measure. If you have already started your program without taking a baseline measure, however, you can compare your metrics to results reported in other parts of the business or industry benchmarks. For example, let’s say you have implemented blind recruitment in one department of your organisation. Ideally, you will have baseline measures to track the impact of that initiative. For example, comparing the number of applicants from monitored groups that make it to interview stage pre- and post-intervention. However, if you have not tracked those figures before the intervention, you can compare the number of applicants for monitored groups that make it through to interview stage in the department implementing blind recruitment with the number of applicants for monitored groups that make it through to interview stage in departments who have not made any modifications to their recruitment practices. It is also important to ensure impact metrics on your own metrics, so in case of the Technology field if the programmers from university / college has a 20 / 80 ratio it is impossible to obtain 50 / 50 ratio in your department, that is the reality. If you want to change this you will have to work at other levels, else change the expansion metric company wide, is there other departments that has a 80/ 20 ratio... it is not a bad thing if everyone values align and works together for a greater purpose and helps each other as a true community should function. Some things takes more time than others... Rome was not build in a day, but they did start working on constructing it. 

Note: on salary scale if you really want to put you money where your mouth is, follow the example of some companies and publish everyone's salary. If you want people to earn the same on the same skill level and experience, this is the way to enforce great career plans for people to earn the same, if person X earn 10 because they have 4 years programming, and 4 other skills, then if Y want the same that is an easy open discussion. In sales if someone work 60 hours a week for 10x and brings in 100x then the same should apply to everyone that are great sales people that can bring in the same amount. Skill training and knowledge sharing should all be helping in this case.

TRACKING

It is important to have a formal plan for measuring your progress—what metrics will be calculated, by whom, and how often? However, merely tracking and reporting diversity and inclusion metrics is not sufficient. The resulting data must also be analysed to assess what is working and what isn’t with the findings used to determine what modifications or additions to the initial action plan are required. It is important to assign responsibility for reporting the findings are well as to define the process for responding to findings. For example, findings are analysed by Human Resources and reported to the Diversity Committee who are tasked with responding to the findings with an action and accountability plan. 
Once targets or other goals are set, responsibility for their achievement should be assigned to individuals who are held accountable through scorecards and other performance management tools. Ultimate accountability for diversity and inclusion should be at the level of the CEO and the Board of Directors.

OBJECTIVES / RESULTS


Goal setting theory (Locke and Latham) posits that motivation and task performance are positively correlated with setting specific and measurable goals. To elicit a behavioral change, people must have a clear idea of what is expected from them. Goals help individuals to focus their efforts in a particular direction. Well-defined and measurable goals are particularly important in diversity and inclusion because, as noted at the beginning of the article, without goals, our automatic and hidden tendencies that preference some over others would easily override our conscious intentions to be fair. That is not to suggest goal setting is easy, however. Setting diversity targets and goals is difficult and needs to be done with caution. Goals should be ambitious enough to encourage effort and commitment but realistic enough so as not to trigger negative emotions such as resistance or fear. When setting goals, consideration must be given to barriers that can be addressed in the short-term and those that will take longer to dismantle.

Report results and outline new initiatives. Results of diversity efforts should be transparent internally. This fosters trust and encourages accountability. Sharing results externally can also be valuable for industry bench-marking and strengthening employer brand and an organisation’s reputation in the marketplace. Of course, not all metrics need to be disclosed, and consideration needs to be given to the costs and benefits of disclosure of a particular metric. Organisations should remain alert to the possibility that not disclosing a metric may erode trust more than disclosing a potentially unfavorable metric. Employees sense when an organisation is hiding something. Not coming clean on a poor metric out of concern of employee backlash might do more harm than good. Organisations that have experienced a diversity failure or missed their diversity targets should respond honestly and sincerely, outlining a plan for rectification. Negative messages are best delivered by the CEO. While there is no hard and fast rule on the frequency of reporting diversity and inclusion metrics, ideally diversity reports should be published at least yearly. Thus review metrics regularly

References:

Friday, July 3, 2020

Microstrategy - Optimize Report Runs (VLDB Settings)

In Microstrategy there is a lot of setting that can tweak the performance runs, but you need to always keep in mind if you choose one you need to keep the standard throughout.

VLDB (Very Large Database) Properties are settings that allow you to tweak some of the detailed behavior of MicroStrategy. These settings control options on how the SQL Engine and Analytical Engine behave, and are necessary to address in every environment. Precise control of these options can have dramatic effects on the performance of your reports.

In all of the places that you can modify VLDB Settings, you want to be sure that you’re seeing all of the available options. From within any of the VLDB Property windows, enable Advanced Settings via the Tools -> Advanced Settings menu item.

Places to Set VLDB Properties

Database Instance Level
This is the most common place to set VLDB Properties. From the Project Configuration screen, choose Database Instances, select the Database Instance you want to configure, and click VLDB Properties. Be careful though, because these settings are applied set as the defaults for every Report in every Project that uses this same Database Instance. This can only be done by architect level.

Attribute Level
There are a few settings that are available per Attribute that override the DBI level settings. These settings are available from within an Attribute via the Tools -> VLDB Properties menu item.


Report Level
You can override the DBI level and attribute level settings for individual reports. Not all of the same settings are available, but the majority are available at this level. These settings are available from within a Report via the Data -> VLDB Properties menu item.

The following are performance tweaks:



Query Optimization -> MD Partition Prequery Support
If you use Metadata Partition Mapping, I find that I get much better performance form the prequery using the option Use Constantinstead of the default Use Count(*).

Query Optimization -> OLAP function support
This is a strange setting. It doesn’t give much detail, but it says that it’s recommended to change in 9.0+ (even though it’s not on by default in 9.0+). It says that the previous behavior could lead to incorrect subtotals, so I don’t see why you wouldn’t want to change this one.

Select/Insert -> Attribute Form Selection Option for Intermediate Passes
This option will allow for the Description forms of Attributes to be selected in individual passes instead of picked up at the end. This can save the need for joins at the end and I’ve found that it increases performance in most situations.

Select/Insert -> Attribute Selection Option for Intermediate Pass
Same as above, this option will also grab any parent Attributes in the same pass as the data, instead of doing extra joins at the end to get those display values.

Select/Insert -> Custom Group Interaction With Report Filter
This option was new in 9.0 and controls whether the Report Filter is applied to the filters contained in a Custom Group. I want this setting the majority of the time, and if there is a specific case when I don’t, then I can override this setting at the Report Level.

The next are not performance tweaks, but they can speed up development or bug fixing:


Joins -> Cartesian Join Warning
The default option is to allow reports with Cross Joins to execute, but I find this to be a bad practice. I prefer to change this setting at the DBI Level to option #3- Cancel Execution only when a warehouse table is involved in either side of the cartesian join. The reason I use #3 over #2- Cancel Execution is because I do want to allow temp tables to cross join since that’s how the SQL Engine handles outer joining metrics in some cases. However, there’s very rarely a legitimate reason to cross join warehouse tables. Instead of allowing a report to run that will either waste system resources or give the user an incorrect result, I’d rather the report just fail immediately. An example of a time where you would want to allow a warehouse table cross join is if you’re using the Report setting to Preserve Attribute Lookup Values. In order to do this, the Report requires a cross join, so in those cases you can override the DBI Level setting by changing this property to option #1- Execute for that Report only.

Metrics -> Default to Metric Name
This setting is purely personal preference and has no impact on performance. By default, this option is disabled which gives you metric aliases of WJXBFS (fun fact: these are the initials of some of the original SQL Engine developers). This can make it very difficult to debug SQL, especially when using Multipass SQL. Enabling this option will instead use the name of the metric as the alias. Just consider the limitations of your database platform, as some have a limit on the number of characters that a column can contain.

Metrics -> Metric Join Type
By default, this is set to Inner Join, which means if you have 2 metrics from different passes, any attribute elements they don’t share in common will result in dropped rows. Personally, I prefer Outer Join as the default here so that I can ensure that I am seeing all of the results. There can be some cases where this has a negative performance impact, so on poorly performing reports, if I’m positive this won’t result in losing data, I’ll change this back to Inner Join at the Report Level. I find that I very rarely have to do that though.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


MicroStrategy publishes technical notes for nearly all of the certified Warehouse platforms that recommend the settings you should apply. Some of the popular ones: Netezza, Oracle, SQL Server, Teradata. For more, just search for “recommended vldb” on the MicroStrategy Knowledge Base.














Friday, March 25, 2016

MicroStrategy - How to use the Bursting feature Distribution

Bursting is a feature introduced in MicroStrategy Distribution Services 9.3.0 that generates page-by slices and stores results to separate files via a subscription.

Reports and Report Services Documents can use the bursting feature if they contain page-bys, and files produced with bursting can use variables to generate unique, individual file names.
  1. Choose a suitable Report or Report Services Document and create a new subscription
  2. Under the "File" heading, select "Add file subscription"
  3. Directly under "File Name" click the button labeled "Burst..." 
  4. The selector boxes will show the Page-By attributes in the left box.  Move attribute from the "Available" box to the "Selected" box to burst according to those attributes.  Selected Attributes will show in the bar above the selector 
  5. The file name can be defined in the File Name blank using the following variables:
      • Bursting Attribute: {[Attribute Name]@[Attribute Form]}
      • Date: %%Date%% or {&Date}
      • Time: %%Time%% or {&Time}
      • Recipient Name: %%RecipientName%% or {&RecipientName}
      • User Login: {&UserLogin}
      • Subscription Name: {&Subscription}
      • Project Name: {&Project}
      • Prompt N: {&Prompt#&}
      • Content Name: {&ContentName}
      • Content Details: {&ContentDetails}
  6. After filling in the remaining required File Subscription options, click OK to accept these options

Thursday, July 10, 2014

BI Report Requirements Gathering

Projects can run into problems if the initial Requirement was not properly gather, spec and understood. In the past years these are the best question that I came across to gather proper requirements:
As in Business Intelligence and Data Warehousing projects, a time exists for discovery of what is wanted and/or needed followed by a time for building what results from the requirements. In typical project management best practices, this discovery process is done through a phase called "requirements analysis”.

While requirements analysis tends to be a very broad topic, this insight will specifically focus on gathering the information requirements which are resulting in a conceptual information model. After all, the information model is the beating heart of any Business Intelligence solution. Its added value largely depends on the quality and the depth of the dimensions & the facts and their corresponding attributes & measures. On the other hand, it also goes without saying that the information model will dictate other key activities such as interface building, Extraction-Transformation-Loading (ETL), creation of a physical model and last but not least front-end BI modeling & reporting.
In terms of specifying information requirements, are the problems we are having today any different than what they were in yesteryear? Hardly. Let me take you back to an interesting report produced by the EDP ANALYZER in July 1977 (Vol. 15, No. 7) entitled, "Getting the Requirements Right." The report highlighted development failures based on poor requirements definition. An analysis was provided of the types of problems when specifying requirements:

                                                               % of Total Errors
  • Incorrect Requirements                 34%
  • Missing/incomplete/inadequate      24%
  • Unclear/ambiguous                       22%
  • Inconsistent/incompatible                9%
  • New/change                                   3%
  • Outside scope of project                4%
  • Typographical errors                      4%


Here are the questions:
  • How many users will be accessing the reports and dashboards?
    • How many users will be accessing the system concurrently?
    • Who are the key and/or influential users? 
      • Their acceptance is very helpful in attaining overall acceptance?
  • What types of users do you have? 
    • Static report users
    • Analysts
    • Developers
    • Executives
    • Who else should get this report?
    • Who else should be aware that this report exists?
  • How will they interact with the system?
    • Interactive data exploration
    • Static report consumption
    • Will the user ask for this report again?
    • Should we schedule it to automatically be sent?
    • Should we make it a self-service report?
    • What fields should we let the user customize?
    • What fields should be limited in options?
  • Where will the content be delivered? 
    • Web/Portal
    • E-mail
    • Mobile
    • File System:
      • Excel
      • Word
      • PDF
  • Response Time: Is there a minimum response time? - specifying the maximum elapsed time from start to finish. (Note: this is not a measure a machine throughput).
  • What are the goals of the users in implementing BI? 
    • Why are those goals important to the business?
  • What is their tolerance for error? 
    • Finance Groups: Zero tolerance for error. 
    • Others: 80% tolerance
  • What is the group’s willingness to work with new dashboards/report? 
    • Are they excited or skeptical? 
    • Do they view it as a help or a threat?
    • How long have they worked with the original Reports?
  • What tools will be used? 
    • Is training required or are users already familiar with the tool(s)?
  • What infrastructure is in place and what is required? 
    • Infrastructure: 
      • Do you have the servers and hardware necessary? 
      • Do you have the people and processes in place to support the solution?
  • Who will be doing the development? 
    • In-house 
    • Outsourced?
  • Do you have a dashboard/report Roadmap?
    • Short-term 
    • Long-term needs 
    • Guideline for all BI related activities?
    • Policy information: Supports executive actions/decisions
    • Control Information: Implements policy and oversees operations
    • Operational Information: Represents the daily routine affairs of the business.
  • What are your business objectives?
  • How would you interpret data set results?
  • How should the data you work with be organized? 
    • Customer
      Will this report filter by customer type? (B2B, Consumer, Retail, OEM, Distributor)
      Does this report need to use the 80 / 20 rule? (Top 10, 50, 100)
      Will this include customer returns?
    • Product
      Will this report filter by product types? (Product Lines, Product Segments, Bulk)
      Are we looking for a specific product?
    • Region
      Will this be local only or International?
      Will this have regional groupings?
      Will this cover where the product was sold or shipped?
    • Time
      What is the date range you are asking for? (Last month, last quarter, last 6 months)
      Is this covering fiscal periods or calendar periods?
      What level of detail do you want displayed? (Days, Weeks, Months, Quarters, Years)
    • Account
    • Person
    • Distribution
    • Channel
  • Offset - specifying the beginning of the cycle. For example, for a Monthly Sales Report, it will most likely begin on the last day of the month (as would payroll calculations). 
  • What are the hierarchies, rollups or aggregations used with these dimensions? 
    • Do customers roll up to geographies that roll up to total? 
    • Do products roll up to product groups? 
    • Do people roll up to districts that roll up to regions? 
    • What types of summary reports do you work with?
  • What are the measures or facts you work with and how are they defined?
    • Revenues
    • Expenses
    • Balances
    • Variances
    • Percent 
    • Measures
      Will this report include currency?
      Does it need to be in the local currency?
      Will we be using the financial Gross or Net?
      Will this report include Units?
      Do we only need finished/complete units or should we include Work In Progress (WIP)?
      Will this report include orders?
      Will it include sample orders?
      Will it include replacement orders?
      Will this report include Visitors?
      Will it include page views?
      Will it include unique customers?
      Will this report include Lifetime Value?
      Will it include customer ranking?
      Will it include customer status? New, Existing, Lost?
  • How do you “filter” the data? 
    • Do you need data only at the top and bottom accounts? 
    • Do you review the performance of only certain types of products? 
    • Do you segment the data based on demographics?
  • Frequency: How often do you obtain refreshes of the data? 
    •  Daily: 1D - Once a day 
    • Daily: 6D - Six times daily 
    • Weekly: 1W - Once a week
    • Monthly: 1M - Once a month 
    • Quarterly: 1Q - Once a quarter 
    • Yearly: 1Y - Once a year
    • Ad hoc: R - Upon Request ("any time I feel like it")
  • Is the data clean?
  • Do you receive the data in a timely fashion?
  • Do the tools you use support your requirements?
  • What types of things would you like to do that you can’t do today?
  • What is your data availability?
  • Do you spend most of your time on analysis or preparation for analysis?
  • Exceptions: There is always some type of data to exclude. What should be excluded?

The initial requirements are identified through interviews, with a representative set of end users. In preparation for the interviews, it’s often useful for the end users to collect a sample of the reports they work with in reporting and analyzing their data as well as screen shots of any tools they use.

Once these question have been answered the specification should start looking as follow for each report:




NAME: (Name of the Report) A meaningful title for the report (e.g. Active Customers Name & Address)
NUMBER: (Report number)
DESCRIPTION: (Full description of the report) A concise description of the report that includes:
    The primary "thing" being reported on (e.g. customer)
    The subject of the attributes being report (e.g. name and address)
    The main criteria (e.g. status = active)
    For example, "The Active Customers Name and Address report includes the names and addresses of all customers with a Status of "Active" and is grouped by State of residence."
BUSINESS PURPOSE: (specifying the knowledge needed; and "why" it is needed)
BUSINESS PROCESS: Which business process is supported
The business process matrix which gives an overview of the combination of all relevant dimensions with all business processes within a certain subject area. A check mark in a cross-section points to a relevant relationship. Given the risk of duplicate data, more ETL development work, more upload time, different labelling and different terminology, it is important to use business processes here instead of departments.


ACTIONS and/or BUSINESS DECISIONS: (specifying "who" must perform the action and "when")
BUSINESS OWNER: The person that will approve the creation of the report, and likely any future changes.
TECHNICAL OWNER: The person that will build the report, and likely any future changes.
Is there a Service Level Agreement (SLA)? - This will dictate how much effort will be done to log report success/failure, does this warrant somebody getting a phone call at two in the morning to fix it, and how many resources should be thrown to break/fix it.
BENEFITS: (defining the tangible and/or intangible benefits of having this knowledge)
NOTE: This textual description validates the need for the information.
TIMING: Specifying Frequency, Offset, Response Time Schedule If the report is deployed to a "report server" should it have one or more standard schedules on which it runs?
TYPE INFORMATION: Policy, Control, Operational
INFORMATION DETAIL: Level of detail (grain)
Grouping If grouping, what are the group levels? How should each group be sorted? Should the group have subtotals and if so which columns and what is the calculation if complex (e.g. weighted average)? Should there be a page break and/or line before/between/after the group?
Sorting How should the report be sorted (which column(s), in what order, and which direction - ascending or descending)? Should the user be able to specify the sort order (dynamic sorting)? If grouping is present, what order should the groups be sorted? Is run-time sorting allowed on a column after report execution?
Parameters:  User supplied values that will change the population. For each parameter
  • Name as it would appear on the screen
  • Format - Number, date, text, etc. Also is it free-form text, or populated from a range of values?
  • Cascading - Are parameter 2 values dependent on what is selected in parameter 1?
  • Should an ‘All’ option be added?
  • Can users select multiple values?
  • User security - Does any of the above require users to have access to only a specific set of parameters, such as the Seattle boss can only run it for Seattle?
  • Filter Criteria What filter criteria (WHERE clause) are there if any? For example, should only "active" customers be included. Another example would be "completed" orders. Should these criterion be evaluated using an AND operator or an OR operator? The column(s) that the filter is applied to should be clearly stated. Label/text for the parameter, Drop-down, text, yes/no, Are multiple selections allowed? What are the values or where do they come from (static list of values or from a table) Should we assume that all criteria are applied to the result set or are the "OR" conditions? Does one parameter drive the value list of another parameter (e.g. Country changes values in state/region parameter drop-down)? What column or field in the result set should this parameter/filter be applied to?
LAYOUT: Column order, Landscape or portrait, Cross-tab or row/column, Headers and footers, Image/logo, Creation date, Page number and format (p. 1, page 1 of 10, etc.), Selected parameter value
Style sheet / cosmetics to use - most corporations have a corporate identity, this means that there should be a standard template to be used.
RECEIVERS: (representing the "consumers" of the information)
OUTPUTS: Since information is ultimately conveyed through some form of output, be it a screen, paper, audio, or other, now we can begin to determine what outputs (one or more) will be needed to satisfy the information requirement (be it a new or existing output).
Accessibility - How will it be accessed? Export format If the report is deployed to a "report server" what is the default format (Excel, Word, CSV, PDF) that it should be written to? If there are scheduled instances, what format should those be written to? Recipients (burst or data-driven - what is the logic?) If a scheduled report instance is burstable or a data-driven email subscription (SSRS) who are the recipients? Is it a static list or is it data-driven? Charts or graphs? Drill-downs? Links to other reports (drill-through)?
Availability - Once a day at 8 am, 24/7, etc.
POPULATION: “All x's by y for time period z-1 to z-2”. Also note if report values are supposed to match any other report.
DATA REQUIREMENTS: Specifying all of the primary and generated data elements required to support each information requirement. Pay particular attention to generated values, as they have to be traced back to all of the primary values needed to compute the generated items (even if they will not ultimately appear on an output). The information matrix gives an overview of the combination of all relevant dimensions with all facts within one single business process. The cross-sections indicate the granularity or level of detail. A check mark in a cross-section points to a relevant relationship. In the below the atabase table and column name if possible must be included with 
  • Label text for the report
  • Any calculation
  • If it's a link to another report or external source and if so a description of how the link should work
  • If it's a sort able column
  • Right, left, or middle alignment
  • Value formatting (MM-DD-YYYY, YYYY-MM-DD, $100, ($100.00), etc.)

Typically this matrix will be detailed more towards its final conception.
  • When the dimension levels will be detailed further, a check mark might be replaced by a certain dimension level which might be more applicable than the most atomic detail of that particular dimension in relation to a certain fact, or will remain a check mark indicating the lowest level as defined within the dimension.
  • This matrix (likewise for the business process matrix) can also be used by replacing the check marks by priorities, availability, source complexity or cost and as such revealing other interesting viewpoints.
Dimension: sheet covers all dimensions which are the axes or context by which the numeric information, contained in the facts, is analyzed. The most important dimensions & attributes - names & definitions should be known at this stage. If possible the following information should be added: name, definition, hierarchies, volume (of the data), examples, source, owner, distinct values …


Metrics: The fact sheet covers all facts (or KPIs) which contain the numeric information upon which the reporting will be done. These measures are kept at the intersection of the different dimensions and are also reported as such. The most important facts - names & definitions should be known at this stage. If possible the following information should be added: name, definition, version (actual, budget, forecast), delivery frequency, additive, calculation, source, owner, supporting business process, availability …

SECURITY: Any security that must be applied? User Security - Who can view the report. Data Security - Does it contain Personal Information or Personal Identity Information that would require protection?
IMPORTANCE OF REPORT:
M = must have this
S = should have this if at all possible
C = could have this if it does not affect any other requirement of being delivered
W = won't have this time, but would like to have in the future


If you are interested in buying my template: Business Intelligence Requirements Template See Template



INFORMATION DRIVEN DESIGN


After the Information Requirements have been defined in this manner, it becomes a relatively simple manner of designing the system and supporting software. The data and timing requirements of the IR Definition will ultimately dictate how data is collected, stored, and retrieved (processing).




We refer to this approach as "Information Driven Design" because it hinges on the requirement definition. If the information requirement is correct, the resulting design will be correct. If the requirement is wrong, no amount of elegant programming will be able to solve the problem.

Under "Information Driven Design" the emphasis is to work backwards to define the necessary business processes (sub-systems), complete with inputs, logical files and outputs. Following this, each business process is detailed in terms of its procedural work flow, from start to finish (forward design). By the time we get to software engineering, the programs should be fully specified in terms of data and processing requirements.

Gathering requirements rapidly using a sound methodology has numerous benefits:
  • Increased Productivity
  • Improved Solution Quality
  • Rapid Results
  • Longer Lasting Results
  • Enhanced Teamwork and Cooperation
  • Lower Development Costs

SUMMARY:

The success of any Data Warehouse & Business Intelligence solution is determined at the beginning of the project life-cycle  when analysts face the business to gather their requirements. Poor gathering of business requirements is one of today's main Data Warehouse & Business Intelligence project failures. One of the key aspects within all those business requirements is the information model. It requires a good preparation and experienced facilitators & analysts with the right skill set to discover the true business needs in this area. But above all, it requires a specific approach for gathering the needed requirements.

Let's review the basic attributes of information:
  • Information represents the intelligence or knowledge needed to support the actions and/or decisions of the business; it is not synonymous with data.
  • Information is a "consumable" commodity used by human beings.
  • Information is a "perishable" commodity; it only has value to the end-user at a specific moment in time.
  • Information Requirements dictate data and processing specifications.
  • If the information requirement is incorrect, that everything that follows will be incorrect.