SQL, BI, and Information Management

Understanding Data Analysis: Part Three

2013-11-03T11:35:00.001+01:00

Data analysis is a basic skill for any business personel at any modern enterprises. Most of the decision making processes at such organizations lay on a solid analytical background before they reach into the final resolution. Almost all the modern Business Intelligence technologies originate from requirements at the data analysis steps. Learning the data analysis process and concept gives very good meaning for understanding why and how the BI technologies have evolved into the current state.

I have a series of blog entries on data analysis concepts, process, and techniques. This is the third part of the series. This blog entry is motivated and actually based on the following references.

1. Michael Milton: Head First Data Analysis

Part III: Mathematics, Statistics, and Data Mining

One very basic professional background of every data analyst is the knowledge and education in mathematics, statistics, and data mining. There has been a saying that pure mathematical education is not useful for the real world any more. In fact, this "saying" has been completely wrong. Lots of mathematical models, methods, and theories are directly applied to real world scenarios. What could be true is that if one is not paying enough attention to what has been taught at the mathematical lectures, one tends to lose all the key part of how to use the mathematics in the real world. But everyone is good at blaming the theories rather than regreting what has been missed in the courses.

Theories cannot talk (or blame), they only give value.

One good example is histogram. Histogram has been a powerful tool in the mathematical and statistical world. And this tool is also very useful in the data analysts´ world. Histogram is a powerful tool to understand the distribution of data across different groupings. Most data analysis involves large groups of numbers and histogram can be used to start the observations when the analysis starts from a blank piece of paper. Both Excel and R provide good support on histograms. The data analysis function in Excel can help to draw histograms. Using R is also a good choice and R is in fact more effective in diagraming tasks like this. In fact, R is better in terms of finding the groupings in the data set.

Another data analysis scenario where mathematics is very often used is to make predictions. Making predictions is just like finding out the best mathematical models for the problem scenario. There will never be perfect models or equations for every scenario or every object. In the end, it is just a guess. An analyst can always start with the very basic mathematical approach, linear regression. If it is possible to find correlations between certain variables, the linear regression is the best way to make the predictions. However, not lots of things in the real world actually fall into the linear model. So an important step for the analyst is to draw scatter plots and try to see if any mathematical models can efficiently represent the trend. It is also very possible that part of the data points on the plots can be represented with linear regressions while the others cannot. Just like the normal step in the analysis, the analyst needs to break the problem into pieces where linear regression works and where other model works. Of course, different non-linear regression models can also be tested in such cases. Back to the universities, a very good course called "numerical analysis" presents all basic mathematical models for this scenario.

Statistics is also a frequently-used tool in data analyst´s daily life. For example, the practices of falsification, hypothesis testing, is a very good method for dealing with heterogeneous sources of information. Such as rumors that you have heard from various places. Do not underestimate the power of analyzing on rumors. In the business world where there more 99% rumors and the rest is only surprises (yes, there is no truth in the business world), making the right "prediction" and "understanding" of the near future business trend means that the analyst must sit and work with rumors. Navgating around the ocean of lies and rumors, the analyst must learn to draw things on a paper and try to link them into different relationships. Different variables can be negatively or positively linked in such diagrams. Very often the analyst will get a network diagram after this session. This is absolutely right! Things in the real world are linked through causal networks.

The core part of hypothesis testing is falsification. The analyst should focus on eliminating the disconfirmed hypotheses rather than picking the right one. What to do when there are more than 1 hypothesis left after you have tried everything to eliminate them? A simple approach is to decide which one has the strongest support based on all the possible evidences. Evidence is diagnostic if it helps you rank one hypothesis as stronger than another. The analyst needs to look at each hypothesis in comparison to each piece of evidence and each other and see which has the strongest support. Very often, what the analyst needs is to make a ranking matrix. Every evidence is ranked with different values for each hypothesis. Sometimes an evidence can have stronger values than the others. Well, how stronger or weaker is decided by the analyst. This is not rocket science and we are just giving a statistically strongest vote to the alternative hypothesis. One thing very important to remember is that there can be new evidence (which may be very strong and can change the total ranking orders of all the candidate hypotheses). It is the analyst' role to make sure that the new evidence is included in time and the latest changes are communicated.

Besides hypothesis testing, I would recommend to learn about Bayesian statistics and the practices about subjective probabilities. In fact, subjective probabilities is a very practical tool i the real world. When it happens that an analyst is not sure about a judgement or a conclusion (which is very often), she or he normally tends to use words like "probably," "perhaps" and so on. Here is the time when one should consider to use subjective probability. That is, to ask for a percentage number instead of theses words. For example, something like "90% probability" is a subjective probability.

With a set of subjective probabilities collected from a group of analysts, it is very easy to use all kinds of statistical methods to further analyze the scenario. One good tool is the standard deviation. The standard deviation measures how far typical points are from the average (or mean) of the data set. Given the set of probabilities collected from different analysts, the standard deviation tells how different the opinions are.

There are many other powerful tools and theories in the statistical world for every data analyst.

Data mining is based on different mathematical and statistical theories and method. The computer scientists tends to "computerize" the theories and make it into a computing model which is ready for real world usage. Details about data minings will be posted in future blogs (where I will concentrate on each data mining method). Here is a list of typical data mining tools and theories used in the data analysis world.

Bayesian Network
Decision Tree
Neural Nework
Support-vector Machines
K-nearest Neighbors
Clustering
Association Rules

Having a background in mathematics, statistics and data mining has always been a career foundation for all data analysts. The techniques and methods mentioned in this blog entry are only a small portion of the whole ocean of the mathematical world. The important thing for data analyst is to keep learning new tools and try to apply them into their real world scenarios. in the end, it is the business value that decides the success of all techniques, methods and theories.

在Blogsy发表的

Understanding Data Analysis: Part Two

2013-10-27T11:24:00.001+01:00

I have a series of blog entries on data analysis concepts, process, and techniques. This is the second part of the series. This blog entry is motivated and actually based on the following references.

1. Michael Milton: Head First Data Analysis

Part II: Test, Optimize and Visualize

While following the four steps described in my previous entry, every analyst is facing the problem of defensing her/his decision-input by the hard, bloody reality of the real world result. All analytical models, decisions and recommendations have to survive through the examinations when they are deployed and used in the real world. Time proves the truth.

But what about fine-tuning and adaptive adjustment to a defined analytical (or mental) model? The world evolves (if not revolutionalizes), so does the mental models. How to give the analysts opportunities to adjust the models and improve them?

Test, test, and test. Yes, test your models. And by "test" I am not saying using just simulations and paper-based data and examples (made by empirical data). You must test your model in the real world and do the experiments in a real scenario.

In many real world scenario, doing test gives very good sense to solve the problem in a sustainable manner. Making a well-planned and well-executed experiments gives a powerful proof of the analyst's judgements. How to carry out the experiment? The very basic element of experimenting is to do surveys. Making surveys before and after a planned test activity can let the analyst have more insight on how effective the test activity has been.

One good example in Michael's book is to use a few local coffee shops to experiment the analytical models which can contribute to a national wide deployment to all the coffee shops. Before running the experiments, the analyst must define what are the factors to observe and compare. And a baseline data must be prepared in order to compare the results. A set of control groups, in contrast to experimental groups, must be established to ensure the comparison. Selecting the control groups requires the analysts to be very careful. The analyst must make sure that other confounders are not blending into the experiment and make sure that all the control groups are "equally" the same on the confounders. After the groups are defined, the rest of the steps are simple and direct. One just need to execute the experiments and collect result for comparison. And the final conclusion will be clear.

What is confounder, precisely? A confounder is a difference among the target group of the analyst's research. A confounder is a factor that, if you use it to compare, makes the analysis result sensible compared to other factors which are non-confounders. A confounder can be factors such as locations, age groups, gender type, etc.

Doing data analysis with identified confounders is not very difficult. The analyst just needs to use the confounders to break out data into smaller chunks to test the result. Identifying confounders based on a given set of data is not easy. Analysts can typically use a set of mathematical or statistical tools to work it out.

A very typical method, when the analytical model or the scenario for analysis gets complicated, is to define a mathematical or statistical model and to optimize it. One thing to make it clear is that no mathematical model can be made so perfect that all real world factors can be modelled, represented and "mathematicalized". One always has to drop those that are too complicated and focus on the "main thing."

The way that an analyst tries to solve the optimization is to define a mathematical model for the problem scenario and then solves the problem by classical mathematical methods. When it happens that the results do not show to be the most optimal result, the analyst must find out where in the model was set to a wrong assumption or if anything should have been included in the model or if it is the right mathematical model to use. There is no perfect model for all scenarios. The analyst's role is to choose the right one for the specific scenario.

Optimization can be done when it is possible to represent the main factors using a mathematical model. There are many data analysis tools (software packages) that can help a data analyst to find out the most optimal result. The Solver package in Excel is a good example of it. And I shall also list R, SAS, SPSS, Mathematica, Matlab and so on.

To solve an optimization problem, the analyst must define an objective function, find out all constants, variables, and search for other constraints related to the identified variables. In many cases, it brings extra benefit if a visualization of the objective function, together with the constraints, can be drawn on a two or three dimensional space. Visualization gives the best chances to verify the analysis and estimate the most optimal result.

In fact, visualization has been broadly used in the scientific (or, academical) world to understand observations and facilitate key decisions on research directions. The business world (and the BI industry) is following this trend in recent years. Good examples are the population of visual analytics tools such as SAS Visual Analytics, Tableau, and Qlikview.

Data visualization means to show your data with diagrams instead of tables of numbers. Visualization is important and extremely useful in most scenarios. It is equally to say that finding out the right visualization is also vital and decides how the clients receive and accept the observations and conclusions.

Using visualizations does not mean that the analyst needs to make fancy or beautiful diagrams. In fact, the visualization should be clear,simple and straightforward.

How to find the best visualization to pinpoint the analysis? A recommended way is to use scattetplots. Just start by choosing an X and Y dimensions and start drawing the data points. Very often the analysts can find many interesting patterns and are able to define new dimensions. A standard analysis would use independent variables as the X axis and dependent variables as the Y axis. Of course there will be more than one dependent and independent variables. The analysis report normally involves diagrams with different combinations of variables which can help to identify certain patterns in the data points.

There exists many excellent data visualization tools. Besides Excel, Tableau and other commercial tools, open source tools, such as R, can be a very good choice.

在Blogsy发表的

Understanding Data Analysis: Part One

2013-07-28T03:04:00.001+02:00

I have a series of blog entries on data analysis concepts, process, and techniques. This is the first part of the series. This blog entry is motivated and actually based on the following references.

Michael Milton: Head First Data Analysis

Part I: The basic steps

Making decisions at a management position can be either easy or difficult. Using intuitions and always bet on one's luck has been a tradition for many "cowboy" style managers. To keep being successful and stay at the right career steps, many managers choose to use decent approaches and make decisions based on a set of rational and serious analysis. This starts the era of data analysis.

No matter what analysis it is, the very basic steps of any analysis are to define the problem, break it down, take observations and make partial solutions, assemble the results and make the final conclusions. Data analysis is about applying the analysis with a large set of data at hand.

The information age brings massive amount of data to every business entity. Data means value and opportunities if you know how to understand and use it. In many situations we found ourselves working as a data analyst without knowing the basic techniques and practices about this role. A data analyst knows how to break down a large set of data and give it a structural understanding so that the data becomes intelligence and insights. With a powerful toolbox, a data analyst transforms data into knowledge which often pushes forward decisions to be made. Thus, the basic skill or instinct of a data analyst is the capability to understand and structure data.

Given a scenario or just a piece of data, the typical steps that every data analyst will go through can be summarized below.

1. Define the problem

This is the very first step. Know your problem is the basic instinct for a data analyst. All analysis is targeted at a goal, that is, the question that should be answered or the problem that should be solved. Without defining the question or the problem, the analysis will head for nothing. How to define the problem? Just ask! You should of course ask the person who will make decisions based on your analysis. Sometimes it can just be yourself if you are to make the decision. Don't just ask for the final goal of the problem. Ask what the decision maker means.

In many scenarios, the decision maker needs the help from a data analyst because she/he

- Do not know about the data

- Know somethinng about the data but is not totally sure with it

- is not sure about the problem

- is not sure about the scenario of the problem

- Is not good at making decisions

- Use more intuition than analytics

The data analyst should start by clearly defining the problem with her/his clients. It can be that the client brings more problems or requirements after this step, which is totally fine. Changes to requirement may bring little overhead in terms of administration effort. But it also helps to narrow down the problem in many cases. A lot of the times, to help the clients to identify her/his problem is the very first and the most important task for the analyst. Many clients are even not aware of the problems that they are facing.

There is, of course, a method or concept called "exploratory data analysis." That means the analyst must find valuable hypotheses from the data which worth evaluating. This is already more than a concrete problem.

2. Disassemble the scenario

To find a solution to a problem scenario, a very typical approach is to divide the problem and scenario into small pieces and analyze. This step requires the data analyst break down the problem into smaller pieces in order to start the detailed analysis. It is important to keep breaking down to the level that best fit the analysis.

To break down the problem, one approach is to create a kind of tree structure, starting with the problem defined in the previous step and extending to different sub-nodes until you reach the leaf level.

This also applies to the data. The data analyst will need more detailed data if the first step only provides a general level summary of the data. More data brings more room for analysis and testing of conclusions.

What is the most important technique to use when looking at data? Making comparisons! In fact, making good comparisons is at the core of data analysis. A data will always find it useful to break down the data by finding good and interesting comparisons of data.

As a product from this step, I can imagine that the data analyst completes with a document of two sections. The first is the break-down structure of the problem. The second is a set of rules on how to divide and slice the data.

As it always says, you cannot move the whole mountain all at once, but you can move it piece by piece.

3. Evaluate your conclusion

This step evaluate all the observations from the previous steps. The key to evaluate is to compare. With all the problems on the left hand and all the observations on the right side, compare, compare and compare!

A compulsory to this step is to insert the analy her/him-self to this process. This means the analyst bring her/his responsibility on board and focus on getting the best value for the client as well as the analyst her/him-self. While the analyst is indeed betting on her/his credibility, there comes also more likelihood that the analyst will get more trust and belief from the clients.

I'd like to quote this paragraph from the book Head First Data Analysis.

"Whether you’re building complex models or making simple decisions, data analysis is all about you: your beliefs, your judgement, your credibility"

4. Make the decision

At this step, the analyst summarizes the conclusion and gives the final decision or recommendation. All the analysis results must be formed into a format that is easy for making decisions. Otherwise, the analysis was useless.

The analyst must make it simple and straight for the decision-makers. It is important to get the voice heard (that is, to make it understandable) so that people can make sensible decisions based on the input from the analyst.

One more thing to add is about the analysis report. The analysis report can take as simple as three sections, " background," "understanding of data," "recommendation." And again, it must be simple, concise and direct.

With all the steps of data analysis, will we be ready for all kinds of challenges? Not exactly. There are quite a list of things to set ready for. One first question will be "What to do when my analysis turns wrong or incomplete?"

Well, not everything can be so easy and safe. In many cases, the initial data analysis report can get bad feed back and results. What could go wrong then?

The first thing to consider is the assumptions made in the previous analysis. Assumptions are usually based on mental models we built on the scenario. Mental model is how we normally see the world around us. It helps us to understand and interpret information around us. It is like a model in mathematics, where one can build a ton of basic elements and describe the world with them. Very often, the analyst's mental model is affected by the mental model from the clients. So an important lesson for the analyst is to make all the mental models, at least the important parts, explicit to the analysis.

Every part of the analysis, such as the statistic model, the data model, etc. is always dependent on the analyst's mental model. When mental model is wrong, all steps of analysis must be run again to provide a new round of analysis result.

To build the right mental model, the analyst must include both things that are taken as assumption and things that are unknown or uncertain. The "anti-assumption" is very often more important and can lead to new discovery and insights. For example, consider making a list of "things I do not know," "things that I do not know how to do," "things that I have not done," etc. This must be applied to the analysis process and the analyst needs to challenge the client on many aspects of the clients' mental model at the "disassemble" step of the analysis.

And of course, the analyst must ask for new and more detailed data for the new round of analysis. Results from the previous analysis can shed the light on where and how to ask for the new data. A very typical mistake from many analysts is to focus too much on the numerical values (also called "measures") in the data. In fact, the row or column header information is even more important to look at. In many cases, how you drill into and mesh around data decides how the data look like in the end.

Analysis never stops. If the analyst always finds out new clues or observations, she/he can continue the four steps for ever.

在Blogsy发表的

A Simple Tutorial on PowerPivot (4)

2012-08-11T21:37:00.000+02:00

5. Publishing and Sharing Reports

After doing analysis on the data, it is the time to share the reports and data to other business users. As mentioned early in this tutorial, it is very important to use SharePoint to create a BI collaboration environment in order to protect the Self-service BI from being a pure spreadsheet mart.

Before publishing PowerPivot workbook to SharePoint, it is normal to consider "beautify" the workbook so that other users find it useful to read and use the report. For example, it is useful to remove or hide worksheets that are not part of the report, to improve the placement and appearance of the worksheets, or even turn of the grid lines to make it better for using. Another important thing to do is about the configuration of data refresh. The connections that you have at your local PowerPivot for Excel require certain data connection providers, and these connections, after being uploaded to SharePoint, still require the same data connection providers or at least require a re-configuration of the data connections. So it is important to install (or ensure that they are installed) the relevant data connection providers at the server.

When publishing PowerPivot workbook to SharePoint. There is an option to turn on document versioning in SharePont 2010 site. However, you should be really careful when deciding this option. After turning on the versioning settings, every change to the PowerPivot workbook is saved to the content database. That means many versions (at almost the same size) of the PowerPivot workbook is saved to the content database. This can make the system heavy loaded after some time.

To load the workbook to SharePoint, choose to "Save & Send" in the Excel Ribbon or just upload the workbook to the site. You should use the PowerPivot Gallery at SharePoint to keep the PowerPivot workbooks.

At the SharePoint server side, you must make sure that relevant features and system services are installed in order to use PowerPivot. To be more specific, Excel Web Access, Excel Calculation Services, PowerPivot System Services, and SQL Server Analysis Services are involved when users interact with published PowerPivot workbook at PowerPivot gallery. Here one can have the same "slicing-and-dicing" , "soring and filtering" user experiences as the PowerPivot for Excel). As illustrated in the example below, one can still work with the “slicing-and-dicing” feature inside the web page open by Internet Explorer.

The PowerPivot Gallery is designed for end-users of Self-service BI to use the PowerPivot. So any user of the workbook, with provided access, can re-use the data and even do further analysis based on it. One can click on a specific sheet in the PowerPivot workbook to further analyze the data with given features from Excel Services.

The PowerPivot Gallery has three standard views, i.e., Gallery View, Theatre view, and Carousel view. The following picture shows the differences of these views.

After a PowerPivot workbook is published at SharePont site, users of the published workbook can create their own reports based on the PowerPivot workbook data (meaning that the workbook can be used like a data source). When you hover on the icons of the workbooks, you can see a option to create new report. Then there are two options available, Open New Excel Workbook and Create Report Builder Report. Below is an example.

When you create an Excel Workbook by using the PowerPivot workbook data, the data is provided from the PowerPivot workbook (in SharePoint) in the same ways as provided from an Analysis Service database.

When using Report Builder to create reports based on PowerPivot workbook data, the data is treated as a data connection just like data connections.

Another important work when sharing your PowerPivot report in SharePoint is to consider refreshing data at a PowerPivot workbook stored in SharePoint. You can easily manage the data refresh in the PowerPivot Gallery. The PowerPivot data refresh feature currently does not support data refresh within a day (shame, isn't it). But you can perform an "immediate" data refresh by manually initiate a data refresh command. And you can also configure the SharePoint to send you an email notification after data has been refreshed. One thing to remember, if you use linked table in PowerPivot workbook, it will not have any data refresh. Below is an example page of data refresh configuration.

6. Conclusion

This Tutorial is made to describe the key features in PowerPivot in simple words. Apparently one can look into the Microsoft site or Youtube to look for more insights or "live-actions" on these features.

Hopefully this tutorial can be help for someone looking to start using PowerPivot. Good luck and enjoy the world of Self-service Business Intelligence!

A Simple Tutorial on PowerPivot (3)

2012-08-10T23:00:00.000+02:00

4. Analyze with PowerPivot

The key feature that makes Excel an ideal tool for business analysis is the PivotTable and PivotChart capabilities. Now with Excel 2010, the “slicer” capability (as illustrated below) is also added so that the interactivity is further improved.

PowerPivot provides a set of options to create PivotTable and PivotChart (as illustrated below). Details about each option is out of scope of this tutorial. It is very similar to Excel with more enhancement on slicer and “measures” which can be created by DAX.

One important option, is to create “Flattened PivotTable”. One usage of this option is to print out the data. Another way is to convert the result PivotTable to Excel Formulas so that one can apply Excel functions on the result table. The option is illustrated below.

It is important to note that the “Field List” for PivotTable or PivotChart under PowerPivot workbooks is different from what it was for other data in Excel. The following picture is an example. The left one is a typical PivotTable Field List while the right one is what you can see in the PowerPivot Field List.

Another interesting feature in PowerPivot is Automatic Relationship Detection. As illustrated below, PowerPivot is being active to detect any potential relationships among tables and fields.

As the core part of data analysis, defining and using measures if always compulsory. PowerPivot provides full support through DAX expressions. Besides the implicit measures which are created while dropping things into the Value area in Pivot chart or Pivot table design. One can also create explicit measures by explicitly specify the DAX expression. To begin creating a measure, right-click on a table name in the PowerPivot field list and select Add New Measure (as illustrated below).

And defining DAX measures requires to use the DAX functions. As illustrated below, the measures are defined in a similar way to Excel formulas.

What to be careful with here is that you should not thing in “rows” when measure values are defined here. And this measure must be defined with an aggregation function (such as sum, count, average, etc.) that will return a single value. In such sense, this is a bit different from the definitions of some calculated columns. This is how it looks like after this measure is defined on the PivotTable.

One more exciting feature to mention, Excel 2010 allows users to create “Named Sets” when using PivotTables (illustrated below). This link provides more detail about this feature: http://blogs.office.com/b/microsoft-excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

PowerPivot can also work with Named Sets in Excel.

A Simple Tutorial on PowerPivot (2)

2012-08-09T22:34:00.000+02:00

3. Enriching the Data

After importing data, the next natural thing to do is to integrate and enrich the data. There are following ways to enrich data.

First, it is possible to change the actual data types for imported columns. As illustrated below, after choosing a column in a PowerPivot table, one can choose the “Data Type” option in the ribbon to convert the data types.

Compared to changing data type of columns, a second approach is to just change the way data looks like in the table. That is done by re-“formatting” the data which only changes the “view” of data without converting the underlying data type. Formatting changes in PowerPivot are applied to an entire column. This is illustrated below.

One of the key features of PowerPivot is to create relationships among tables. As illustrated below, it is possible to set up relationships after the PowerPivot tables are imported. The “Create Relationship” option at the “Design” ribbon in PowerPivot window allows one to set up relationship between tables. Due to the limitation of the current version, relationships can only be defined on single columns (as shown below). This means that if you want to establish two distinct relationships between two tables, you must import another instance of the table. It is a bit similar to using SQL to define join statements that use multiple columns in the same table. So users cannot expect to define complicated relationships in PowerPivot. The “Manage Relationship” button shows all the defined relationships of all the imported PowerPivot tables.

A very existing feature in PowerPivot is that one can define ”measures” (like in Cubes) using the DAX formulas. This includes calculated columns (which are materialized into the table) and measures explicitly defined (which are kept virtual until called). Below is an example of a defined calculated column in a PowerPivot table. The DAX formula in the Fx textbox seems very similar to Excel. But the main difference is that this definition covers a whole column “TotalSales” instead of a single cell in Excel.

A little bit more about DAX. DAX in fact has a total of 135 functions to help with data analysis. About 80 functions are supported by Excel. DAX also provides functions that operate on tables.The DAX functions can be categorized into eight categories, i.e., Date and Time, Information, Logical, Math and Trigonometric, Statistical, Text, Filter, Time Intelligence. Similar to math and trigonometric functions , only a subset of Excel’s statistical functions is supported by DAX in the current release of PowerPivot. When typing DAX formulas, PowerPivot provides the “autocomplete” feature. One needs to press the Tab key to use this feature.

Like Excel, PowerPivot window provides the ”Sorting” and ”Filtering” options at the “Sort and Filter” Section, and the “Freeze” option at the “View” Section for users to refine the data. This is illustrated below. The column width can also be adjusted in a similar way to Excel.

Another useful feature for improving the view is to use ”hide” and ”unhide” options. As illustrated below, it is possible to hide columns from PowerPivot or PivotTable or both.

After data is loaded to PowerPivot, the “Refresh” button can be used to retrieve the latest source data to PowerPivot. As illustrated below, it is possible to refresh the current table or refresh all tables in PowerPivot. When clicking the “refresh” button, what happens behind the scene is that PowerPivot sends two commands to the VertiPaq engine when you click Refresh:

The first command is to “Alter”, i.e., to make any metadata changes you have made in the PowerPivot Window.

The Second command is to “Process”, i.e., to refresh the data from external data sources and any calculations that depend on the refreshed data.

A Simple Tutorial on PowerPivot (1)

2012-08-08T21:41:00.002+02:00

1. Introduction

Using PowerPivot is pretty easy. In general, four steps are involved.

First, you need to get the data to PowerPivot. This is about importing and doing a little preparation on the data. Data can be brought from relational databases, Analysis Service databases, PowerPivot that have been published at SharePoint sites, Excel and Text files, data feeds (ops, something new from SharePoint), through Copy-Paste actions, linked worksheet, etc.

Second, after taking everything into PowerPivot, you need to enrich the data through various ways, such as filtering some rows, hide/unhide some columns, formatting columns (or change data types), create calculated columns through DAX definitions, and most important of all, defining relationships among different PowerPivot tables.

Third, with all data ready to use, it is the time to analyze the data.Here is the time to use DAX to create measures and more Excel functionalities to find out interesting results.

Forth, and the most important of all, create and share your reports.This is about showing and sharing your reports through SharePoint.

This tutorial has four parts where each part introduces one of the above steps.

Let’s start with the first step.

2. Data Importing and Preparation

To bring data to PowerPivot is similar to bringing data to SQL Server Express. You will have to be able to connect to different data sources. PowerPivot requires database providers and, in some cases, client libraries in order to be able to import data from these sources. In most cases, the database providers and client libraries are not provided by PowerPivot for Excel and must be acquired and installed separately.

To import data, the simplest way is to import using Table Import Wizard at the PowerPivot window. There are two options when importing data using the Table Import Wizard (so you should open PowerPivot window and click “Table Import Wizard”.

1. Select from a list of tables or view

2. Specify a SQL Query.

If you choose “Select from a list of tables or view”, the following picture shows the result.

The button “Select Related Tables” gives a big help if you know one of the tables (and have selected it” and wants to make sure that all other relevant tables are included.”

For the selected tables, you can use “Preview&Filter” button to decide whether to include or exclude certain columns or rows from the data. The following picture is an example.

After selecting all the relevant tables and completing relevant filtering options. Click “Finish” button to start the actual importing process. Result of the Importing process looks like the following.

Now, let’s take a look at the second way, i.e., “Specify a SQL Query” when doing the Table Import Wizard. When choosing this option, “Specify a SQL Query”, one can use the “Design” button to go into the Query Designer to specify the SQL Query. The picture below is an example of the SQL query.

In addition, Table Import Wizard lets you import data from MS Analysis Services (MSAS) and other PowerPivot workbooks. It’s important to note that the result of MSAS query is typically a "cellset" object, which is a multi-dimensional object. But when the data is imported into PowerPivot, it is transformed into a simple table. When you import data from MSAS you won’t have the same interactivity of slicing and dicing as you typically have when using BI client tools, such as Excel connected to an Analysis Services Unified Dimension Model (UDM) cube. Another very interesting feature of PowerPivot is that PowerPivot can connect to data in a published PowerPivot workbooks at sharepoint sites. This makes the enterprise mash-ups into reality. It’s worthy to note that you cannot import data from PowerPivot workbooks if they are .xlsx files on a local disk drive.

Besides importing tables, PowerPivot now supports “data feed”. Data feeds is a new type of technology that provides tabular-like data such that users can subscribe to the data in the same way as the RSS feeds in order to receive updated data as the source changes. Data feeds are based on the Open Data Protocol, which uses the Atom Publishing Protocol and its Data Services URI and Payload Extensions. Data feed can come from SQL Server reporting services, SharePoint List and any generic data feed that supports the OData protocol.

Here is an example when you choose to import “From Data Feeds” in PowerPivot. PowerPivot understood the URL description and import the initial data from the source.

Text Files is another important source of data. Just like importing data in DTS, PowerPivot works with both Delimited text files and fixed-length-field text files. But to work with the fix-length-field files is a little bit tricky. One of the key step here is to define a schema.ini (exact name as this) at the same location as the file. The contents of the schema.ini file are described in the MSDN topic at this link

You can also import from Excel files. This is demonstrated below.

There are also other ways to bring data into PowerPivot. First one is to paste data from clipboard (well, you need to copy these tabular data first), as demonstrated below.

Second one is to create ”Linked Excel tables” which keeps the source and target in sync automatically. The following is an example.

And the result of the linked table is shown in the PowerPivot window with a special icon (the one next to the “DimStore” name in the below picture).

After specifying the import criteria, PowerPivot will start the importing with the progress reports. It is important to note here that although PowerPivot can import data from many diverse data sources. The data types supported in PowerPivot is very limited compared to the big variety of data types at different sources. One of the things to be alerted is when you are importing data under different currency types, PowerPivot will import currency types as general currency types (“general” to the regional format defined by the machine where PowerPivot is running on). This can definitely lead to unexpected results. But you can use the PowerPivot formatting tools to adjust the columns’ data type, and format after the import is completed.

Basics About Zachman framework

2012-08-06T21:52:00.002+02:00

With inspirations from traditional architectural and engineering discipline, John Zachman started the Zachman framework with its first publication in 1987. Zachman framework provides a simple (at least it looks simple) but well-structured approach that supports many ways of architectural presentation using a common vocabulary and viewpoints.

To be more specific, Zachman framework describes a model of an enterprise system from six perspectives, Planner, Owner, Designer, Builder, Subcontractor and the working system. The major feature in Zachman framework is this matrix with 36 cells (example picture from Wikipedia) indicating all "possible" aspects to an enterprise system.

Here are the rows of the matrix:
Scope, Business Model, System Model, Technology Model, Components, Working system

Here are the columns of the matrix:
Who, when, why, what, how, where

As indicated in the publications about Zachman framework The major principles behind Zachman framework are:

1. "A complete system can be modeled by depicting answers to the following questions: why,
who, what, how, where, and when."
2. "The six perspectives capture all the critical models required for system development."
3. "The constraints for each perspective are additive; those of a lower row are added to those of
the rows above to provide a growing number of restrictions."
4. "The columns represent different abstractions in an effort to reduce the complexity of any
single model that is built."
5. "The columns have no order."
6. "The model in each column must be unique."
7. "Each row represents a unique perspective."
8. "Each cell is unique."
9. "The inherent logic is recursive."

This framework with 36 cells can be used recursively to management the overall enterprise systems and each components inside it. One can build the 36 cells in different levels to keep views of an enterprise system at different levels. Implementing Zachman framework must recognize two critical challenges in current enterprises.

One must beginning making the descriptive representation of enterprise more explicit. This means to populate the various cells of Zachman framework
Formalize a system to keep populate the different cells in a disciplined way.

There can be two different approaches to get a company a quick-start on using the Zachman framework.

Start developing the cells with the purpose of developing an enterprise architecture strategy
Start developing row 1 of the matrix in order to establish an implementation road map.

As claimed by different parties, one drawback of this framework is that it does not have so much public, open documentation as compared to TOGAF. However, from my own experience, this is also a good part of a framework where certain spaces are left for the enterprise itself to find out and define the discipline. To many enterprise architects, working by using everything available makes their life easier and simple. While to some other enterprise architects, having more space to dig out valuable works for the enterprise is of more fun and self-satisfying.

Understanding TOGAF

2012-08-03T22:30:00.002+02:00

The Open Group Architectural Framework (TOGAF) is considered to be the most often used framework for the future. Here are some notes that I've taken after reading the first introduction of TOGAF.

TOGAF support the most commonly accepted architecture types, i.e., business (or business process) architecture, application architecture, data architecture, technology architecture. A key concept built-into TOGAF is that it is driven to create an open systems-based architecture so that solutions made at an enterprise are not relying on any single suppliers but focused on ensuring an open and integrated solution such that multiple vendors and heterogeneous systems can be added at any time without a major cost or risk.

TOGAF consists of three parts. 1) The TOGAF architecture development method, which explains how to build an enterprise architecture for a specific enterprise in order to fulfill the business requirements. 2) The Enterprise Continuum whch is a repository of all architecture assets such as models, patterns, etc. 3) The TOGAF Resource Base which is a set of resources (templates, guidelines, handbooks, etc.) that facilitates the development of the architecture.

In the TOGAF dictionary, "architecture" is often interpreted as "view" of things. For example, there will be a business view, a data view, or a technology view of enterprise systems. This is quite typical in the enterprise architecture world. Just like the architecture of a building, it is always useful to present the architecture through different "views" to suit the need of different purposes, such as a construction view, a electricity supply view, a networking view, a security view, etc.

In fact, creating and using views is the one of the key activities in TOGAF. Seeing the enterprise as a whole thing, different people (in other words, the stakeholders) tend to looking at this "whole thing" from different view points (i.e., perspectives) because they are different "concerns" (which is the key interest that defines the acceptability of any systems inside this whole thing). The focus inside TOGAF is to ensure that all necessary viewpoints are identified and satisfied. The following three steps have to be repeated very often.

    - Choose key stakeholders
    - Understand and document their concerns
    - Figure out how one models and deals with those concerns

TOGAF provides a taxonomy of views but it does not mean every enterprise (who employed the TOGAF concept) must develop all items provided. In fact, it is always important to "pick to use" what is beneficial to an enterprise, which is determined by the architect team. One of the key points to ensure the success of making different views to different stakeholders is to ensure that everyone use the same language (i.e., architecture terms, elements, definitions) to communicate.

The Business Case of Self-service BI

2012-08-02T22:54:00.003+02:00

Why Self-service BI makes a difference? If we look at the state of BI before, then the answer is quite clear.

Only trained and highly-paid BI developers can be used to create BI applications, which is highly customized (which means cost of maintenance, further development, and even decommissioning).

After BI application is built, it becomes difficult (money, time) to implement changes even the business situations and requirements are changed.

After having 5 different BI applications, you have to have people with specific skills and knowledge to the ground level of these applications. Each system works in its own way. (so what's the cost of maintenance and operating)
Since it costs too much, many business analysts start to copy data from production systems and make their own analysis from local PCs. Spreadmarts become popular. Everyone becomes a cowboy and key business decisions are made from some charts in a spreadsheet saved at some one's local PC.
When people are trying to share the spreadsheet files, the whole business is having the risk of losing confidential data without being aware of it.

Given these scenarios, starting a self-service BI can definitely eliminate the above scenarios by bringing lower cost, shorter time-to-market, reduced IT investment, more precise requirement specifications for corporate BI solutions, and more efficient collaborations among the business users.

To be a bit more specific, here is a few points demonstrating the benefits claimed above.

First, most self-service BI tools start by extending Excel or providing a very easy-to-start interface so that the business users always find it simple and quick to start producing reports, sometimes just like what they have been doing with Excel. And self-service BI means the business users have to serve themselves to produce the end result. There is almost no intervention from IT side, not to mention any "waiting time," "resource bottleneck," etc. This dramatically reduce the development cost for many BI solutions at the business team.

Second, since the development of the BI solutions is at the hand of business users, there is absolutely no communication detour between business-IT-business in order to get most work done. Shorter time-to-market is ensured since the one who needs it is the one who builds it. Who else to complain about it?

Third, the role of IT in this scenario is to monitor the business work and observe the need for creating corporate BI solutions (which is more reliable in terms of maintenance and operation). What's more important is that after doing these many self-service BI solutions, the business is actually making a very excellent prototype for the potential BI solutions that can benefit more. This saved quite much time of requirement analysis, interviews, escalations and much effort made by the solution architects at every project.

Forth, the concept, which is also a key in many self-service BI solution, is that self-service BI must include a collaborative effort to ensure the communications and reusability among the business team and the IT team. This avoids the spreadmart scenario and in fact, provides a chance to maximize the benefit of solutions developed by the power users at the business team.

Oh, one last point to make. What happens to the corporate BI solutions when self-service BI becomes so popular. The answer is, "nothing." We still need corporate BI solution and we still need to develop these solutions through IT and business corporations. But, the difference is, we've got a better overview of what we need and what we want this time ;)

Purpose of having an Enterprise Architecture

2012-08-01T22:34:00.005+02:00

Enterprise Architecture is closely connected to an overview of the whole. It does not matter if an enterprise means a large corporation, or a division, or a department. Enterprise Architecture is to keep an eye on the overall interest of this “enterprise.”

So what’s the business case of having an Enterprise Architecture? One thing for sure is that people tend to follow the trace of “standardization” when an EA process is driving. And this leads to a better commoditization of software and systems within an organization, i.e., controllable operating cost and predictable development time. When having an established EA process, people tend to have better communication effects and are able to make design decisions in shorter time (this is extremely important for large organizations where escalations and politics are popular). And, the most important of all, an EA tells the people, especially the leaders, the directions to go, the initiatives to take, and the strategies to rely on.

Perhaps a better way t to look at this is to see the situation when EA is not in place. Without EA, an organization definitely experiences one or more of the followings.

Many locally optimal, rather than globally optimal solutions exist and cannot removed
Very high operating cost due to the fact that things are not globally shared and re-used
Too much dependency on a vendor or a legacy application (no one knows who's using it, no one knows anything about it, no one dares to close it down)
Most solutions are created for short term purpose and limits the long term benefit

IT standards are not respected

It is worth noting that even lots of IT functionalities are outsourced to different vendors, EA is still vital and even more critical in such situations. With more than one vendors pressing the CFO for different payments, the EA team must guardian the enterprise to avoid being a cash-cow kind.

Basics for Understanding PowerPivot

2012-08-01T22:32:00.004+02:00

I often met the questions about PowerPivot these days. "What is it?" "Why should we have it?" "Can PowerPivot do this for me?"

Well, the best way is to make a list of most basic descriptions of PowerPivot and share with those who need it. So here they are.

What is PowerPivot

PowerPivot is a new data analysis technology in a way that it makes the "impossible" usage of EXCEL (in the past) to be possible now. So business analysts do not always have to wait for a few months to see the end-result of IT implementation. They can instead do self-service and make many of the BI solution implementations on their own (self-service).

Self-service BI compared to corporate BI

PowerPivot (together with Excel, and most importantly, SharePoint) enables the self-service BI capabilities of a company. But this does not mean the era of Corporate BI is over. Instead, corporate BI will have an even more important status since the business is now able to specify more clearly on their requirements and is even able to provide a prototype plus data and results that can be used to validate the IT implementations.

PowerPivot is not a standalone tool

PowerPivot integrates with Excel (2010 since it needs the size extensions enabled from this version of Excel) and SharePoint 2010.

Data Analysis Expressions (DAX)

This is one of the key extensions of PowerPivot (in addition to the in-memory analysis engine and the compression algorithms). DAX is a collection of formula elements, i.e., functions, operators and constants that can be used to make expressions or do calculations. It is also available to the Tabular Model projects.

Managed BI collaboration environment

Clearly PowerPivot has enhanced the possibilities of spread marts and too many standalone worksheets across an enterprise. That's why it is critical to establish a managed BI collaboration environment before promoting the self-service BI concept. Here exists lots of process, rule, training, and SharePointing work. With a SharePoint site, the business can clearly do the self-service work while the IT department puts on certain audit-trails and analysis to find out the need for optimizations, further maintenance and even development of Corporate BI solutions.

Technical specifications

There is no doubt that PowerPivot includes a good in-memory database. Here is the technical specification that one should always remember. "....The maximum file size of a PowerPivot workbook is 2 GB. There are no restrictions on the amount of data users can import into a workbook, but workbooks exceeding the maximum file size can’t be saved. A 2-GB workbook typically corresponds to a 4-GB dataset, considering a 2:1 compression ratio.... Performance tests show that PowerPivot for Excel can load more than 100 million rows and maintain adequate processing performance with 2 GB of memory. However, test results vary depending

on the compressibility of the data. For fastest processing performance, Microsoft recommends multi-core processors and more than 4 GB of RAM...." (this is from the document "Microsoft SQL Server PowerPivot Planning and Deployment").

Key elements in PowerPivot for SharePoint

The Timer service (automatically refresh the data), the PowerPivot web services and the PowerPivot Management Dashboard.

The best picture that shows the roles in BI at enterprises

Look at this one (or use the following URL http://i.technet.microsoft.com/dynimg/IC396763.jpg)

The real key to success

Having a focused effort to delivery training, technical documentation and support to the "Power users," and business users is the real critical factor to the success of self-service BI. A good practice is to making a good knowledge base for common questions and training materials.

Understand the performance gain and loss

What PowerPivot provides a a great performance if everything is inside your memory. To initially get the data from somewhere, there is a potential bottleneck on the network bandwidth. In that case, do not blame PowerPivot but try to decentralize the data just like reducing the heavy traffic around the central station of your city.

Security and access

PowerPivot is definitely bring challenges to those who work with data access and security control . As a "fat-client" layer in an enterprise landscape, it is very important to identify the security needs and utilize SharePoint as a mitigation layer.

6 Questions for Understanding Enterprise Architecture

2012-07-31T23:01:00.001+02:00

Many companies are implementing enterprise architecture these years. IT employees or “senior” IT employees are upgraded to the “shining” title of “Enterprise Architect.” Is it true that Enterprise Architect is just another rank name in deciding the seniority of employees or is it something really creating benefit for a company?

The best way to find out all these answers is to understand what is Enterprise Architecture.

Wait a minute. I am not going to start writing massive content about the definitions, theories, and so on. Instead, I will list the key questions that one should ask about Enterprise Architecture. How about the answers? Well, they are in wiki pages, google results, books, presentations, and most important of all, your own analysis and judgements.

1. To understand Enterprise Architecture, the first question is “what is Enterprise Architecture?” Or, one could start even one step back. What is “Enterprise?” Is it a multi-nation corporation or the IT department or a whole business division? And what is “architecture” or what is “IT architecture?”

2. Why do we need Enterprise Architecture? Of course we know that it is about fulfilling the business requirements. But there can be different ways of solving business requirements. Why do so many of us choose to use the EA approach?

3. When talking about IT architecture or software architecture, many books and articles mentioned about different levels or levels of abstraction. For example, there can be business architecture, solution architecture, reference architecture, data or information architecture, operation architecture, technology architecture, etc. What are the right ways of understanding these levels? How are they connected?

4. Are there any existing concepts, frameworks, guidelines, and systems about Enterprise Architecture (oh yes, there’s plenty and more coming). What are their differences?

5. How about a short history of Enterprise Architecture? Why did we come to this era that we all need the EA. What was it before? How did enterprise IT evolve over the past many years? Besides the history, how’s the progress and new topics in this discipline? Where do I get the latest trend and what are the trend now?

6. What about the business case for implementing Enterprise Architecture? How and why did so many companies accept to implement EA?

OK. Enough about the questions. Here are two exercises if all the above questions are answered.

E1. Consider IT systems and solutions as an commodity is a natural way to implement cost-saving for any enterprise. More and more companies are seeing the need of using "out of the box" software rather than self- developing IT systems as a "piece of art" which can only serve specific purposes. In parallel to this, we have been seeing the new evolution of IT software that enables simple and quick customizations which makes a standard software quickly fit well into an organization with a compromise between the end-users "never satisfied needs", the business sponsors' "cost-reduction while shorter time-to-market demand focus" and the IT technicians' "quality and easy to maintenance awareness”. Providing this scenario (which is very typical in companies), how is EA’s role in such a situation? How should EA solve or resolve or manage such scenarios?

E2. Many companies consider cost-saving and EA is a great area to focus on cost-saving. But is this enough? How about getting more profit? Can we use EA to gain profit for the company?

Initial Impression of Metadata Tools in Information Server 8.7

2012-04-17T23:11:00.003+02:00

Just got my hands on a sandbox of Information Server 8.7 installation. After looking at the InfoCenter and the Redbook about metadata management, I have had an initial view of the new metadata offerings from the Information Server 8.7.

Just to add a bit background here. I have been using Information Server 8.1.x in the past 4 years.

In InfoSphere Business Glossary, I can see that the BG "Editor" and BG "Browser" interfaces are merged into a single user interface which is a good improvement on the user experience side. The new UI looks more "modern" and gives overview to both terms, categories, and IT assets very easily. In addition, Business Glossary has added a workflow feature that enables an "approval workflow" for managing BG terms. This workflow is disabled in the default installation.
The meta-model in the Metadata Server has been extended with a few new elements. Among other, the most existing one to me is the Logical Data Model. In the 8.1.x version, only physical data model (database table schemes) can be imported to the Metadata Server. Now a logical data model created in InfoSphere Data Architect, including the documentations in IDA, can be imported to the Metadata Server. This is a very good step towards making BG and Metadata Workbench more widely accepted and used by both business and IT developers in enterprises.
Metadata Workbench looks similar to the previous versions. The "Administration" tab in Metadata Workbench has an improved interface. And as I have heard (not tried myself yet), the data lineage and business lineage functionality has been improved a lot, so is the automated metadata service (which has been a pain in the 8.1.x version).
A new tool, "Metadata Asset Management" seems to provide an excellent way of administrating metadata in an enterprise setup. This tool allows users to import and compare different types of metadata, such as data models, data files, BI reports. As described in the Redbook (link), one can , based on this tool, design an enterprise metadata environment for all kinds of metadata related to data integration process.
istool seems to keep the same functionalities as before and add a "reporting asset" for import/export.

I also heard that Business Glossary provides more ways of integration with other tools. And the "Blueprint Director" is a very interesting architecture tool. Hopefully I will post more findings when I begin to do more work with this sandbox in the coming days :)

The Need of Overviewing Enterprise Data Lineage

2012-04-09T23:21:00.000+02:00

The concept of data lineage comes to proliferation in the Information Management world due to the need for more detailed, quality-focused metadata of the data flows and deliveries across enterprise IT systems.

In a large enterprise where regulatory reporting, dash boards, score card and analytics are widely used, different IT systems need to delivery their data to fulfill the various purpose of business intelligence applications. As a best practice, such enterprise would already have established a data warehouse team to maintain a central repository of all kinds of data. Use of data warehouse makes the architecture of enterprise data flows quite simple. The warehouse is just like a centralized hub that collects all kinds of input and delivers outputs to various parties. And in such case, managing data flows and data deliveries across the enterprise seems simple (although still very challenging) because all management focus can be put around the data warehouse. Data lineage in such an architecture form is focused on the lineage of data pre- and post- the data warehouse.

An very good example is the data lineage tools inside the IBM InfoSphere Metadata Workbench. In the data lineage tool, a source delivery file can be connected to an ETL job that takes this file to the data warehouse table. Then the table can be connected to the ETL job that loads the data warehouse table to the data mart tables which are then extracted and loaded to BI reports. With a naive (yes, not native but naive) support of defining external applications and extension mappings, the data lineage can be (manually) extended to external systems and tools to include the whole data life cycle, i.e., starting from the front-end system where the data was initially created, to the deliveries, the data warehouse, the data mart, and the BI applications, not to mention the various ETL jobs, data quality checks, etc., during the "life cycle" of this data-to-information flow.

The data lineage functionality in InfoSphere Metadata Workbench does give us an good example of how enterprise users can benefit from having such a view across different architectural elements. Here is a short list:

Traceability of technical elements brought by the data lineage functionality can reduce the cost of system maintenance and support. For example, a developer in the system management group will easily find out all the relevant tables and jobs that are related to a piece of "problematic" data.
The cost of doing impact analysis can be dramatically reduced if the data lineage information is available. Whenever a change is to be applied on an architectural elements, estimating the impact in terms of cost or even possibility can save a great budget.
The capability of aligning architecture designs and to considering optimal refactoring options can be further strengthened if a detailed data lineage map is available. It is much easier for an IT architect to make the design options if the relevant data flows, together with the performance and throughput data, are presented on a diagram (which makes the job of IT architect more or less similar to an construction architect's work).

After seeing the benefits of data lineage, we need to come back and challenge if we need to have an overview of the whole data lineage across the enterprise IT systems instead of only those around the data warehouse.

Typically, most of the data need can be fulfilled by requesting a data delivery from the data warehouse to any business intelligence applications. In many enterprises, lots of IT systems act as a "hub" of certain group of enterprise data and generate outputs by adding "values" to the data. For example, a CRM system or an accounting system normally need to take data like customer, branches, agreements from other systems and then create output data with more "corrected," "aligned," "calculated" information. From an enterprise point of view, the output from such "intermediate" systems is also a main data source for the data warehouse. And the data flows from and to these systems are equally or even more "mission-critical" than the data warehouse.

Another example is the case when there is much need on making "operational reporting" or "operational business intelligence" in a real-time or "near real time" manner. In such cases, event-based architecture is used to send data directly to the business intelligence applications. No data warehouse is involved here (except that an end-of-day summary may be delivered to the data warehouse through a batch-window). Such data flow often requires great architecture care due to its mission-critical purpose.

So, it does make lots of sense to maintain an overview of the whole enterprise data lineage on top of all IT systems, not just around the data warehouse. As up to now and up to my knowledge, there has not been any great software product that can fulfill this need completely. We need to cross our fingers and see what the vendors (I expect IBM, ASG, and maybe some start-ups) can bring to the table in the future.

How to start the implementation of InfoSphere Business Glossary

2012-04-06T23:54:00.002+02:00

InfoSphere Business Glossary (IBG or BG) is a very useful "Data Concept Management Tool" to connect all users of data at an enterprise. BG is part of the IBM Information Server tool suite and is intended to be used as a enterprise glossary of business concepts and terms. With the metadata interchange support and the Information Server Metadata Server, a business term described in BG can be connected to a technical asset such as a column in a table, an ETL job that populates a table, a field member of a Business Intelligence report, or flat file contained in a delivery between enterprise applications. Another key feature of BG is the use of data steward. A data steward (person, role or group) can be assigned as an owner of a business term defined in BG. This provides an opportunity of creating an enterprise data governance structure. The ownership concept provides an enterprise-wide awareness of data and quality of data.

Implementation of BG normally requires a team of allocated resources (e.g., a project team or a task force) to start an initial "enabling" phase to define the work process and to actually implement a substantial amount of key business terms and concepts. The following is a list of key issues or topics that such team must work with in order to create a successful initial implementation of Business Glossary.

1. Creating a data owner/steward role in the enterprise
    Apparently, making terms without knowing if it is correct or wrong does not make any sense at all. The enterprise must have employees that care and use the data that a term describes. Designing a role like data steward will not cost the employee a huge amount of extra working hours. On the long term perspective, the data owner can guardian the data and quality of data which will, in return, helps the enterprise to make the right business decisions based on the reports/dashboards/mining results that are based on the data.

2. Designing a basic category structure in BG
   Business Glossary is a very simple tool. All business terms are located in categories. All categories are having a tree structure. A term physically located in one category can be "referred" by another category. A term can be connected to other terms as synonyms.
   Generally, a good structure in BG contains top categories that classifies the business concepts. For example, there can be a category called "Customer" which includes all kinds of business terms and concepts regarding customers to this enterprise. If all the top categories in BG are about such kind of major business conceptual areas, for example, "Customer," "Branch," "Product," "Agreements," etc., any users in the enterprise will find it not difficult to browse around to understand all the key business areas and business concepts. Besides, the search functionality in BG is well enough for any newcomers to look around.

3. Designing the work processes around BG
    Typically, when users start to use BG, there will be requirements on how to let data steward edit the terms, how to manage the life cycle of terms (in BG, a term can be "standard," "accepted," "candidate," or "deprecated"), and how to make sure that all contents are having back-ups etc. There can be many more such questions to the maintenance team of the BG content.
    In addition, when BG is used and maintained in a strict enterprise environment where there can be TEST, FREEZE and PRODUCTION instances, the work processes seem to be more important.

4. Marketing and making organizational implementation
    A tool like Business Glossary does not contain any fancy and complicated functionality such that all users of the enterprise can suddenly start enjoying and cheering up every time they find it useful. The team must go out and communicate with almost everyone in the enterprise in order to have a good start on the tool. Here, the data stewards team should be the first group that get familiar (and enjoyable) with the tool and start (with their charms) to influence other enterprise users as "Business Glossary advocates" until everybody likes it.

5. Starting populating the terms
    Normally an enterprise must have several key business concepts that are used throughout almost all activities/documentations, such as customer, internal units, employee, etc. It is very unlikely that one cannot find any employee that cares about terms and concepts in these core conceptual areas. In other words, one should find it possible and possibly easy to define and describe terms and assign data stewards in these core concepts. Giving a dedicated period and resource to populate terms in such core business conceptual areas gives a huge positive impact on the success of Business Glossary.

The concept of having a Business Glossary is to use the "crown sourcing" power to improve and align the organization on the data and information usage. It paves the way for successful business intelligence implementation in the long run. Hopefully, we could expect more social and collaborative features from Business Glossary in the future.

Resizing Calendar webpart in MOSS 2003 and MOSS 2007

2011-12-30T22:25:00.000+01:00

I ran into this problem when trying to drag a calendar webpart to a MOSS 2003 page (yeah, it's MOSS2011 now, but someone still prefer to use MOSS2003). To resize the Calendar webpart, I found a very excellent post at : http://blog.pathtosharepoint.com/2008/10/06/tiny-sharepoint-calendar-1/.

The key part of the solution is to use the Content-Editor webpart and try to overwrite the default style-sheet for the calendar webpart.

Running WordCount example in Hadoop installation at cygwin

2011-12-30T00:04:00.001+01:00

I couldn't find a spare PC to install Ubuntu. So a cygwin on my Windows PC seems to be the only solution for running Hadoop (I did try to install Ubuntu desktop on the same PC, and then there is a wireless adapter error). I followed most of the steps that one can find out on Google. To me, the most helpful notes come from http://pages.cs.brandeis.edu/~cs147a/lab/hadoop-windows/

My setup: Windows 7 professional (32 bit), Dell Studio. And I have a single node Hadoop installation on the cygwin environment.

I think most of the steps described in http://pages.cs.brandeis.edu/~cs147a/lab/hadoop-windows/ are still quite correct. But when setting up $JAVA_HOME, I would suggest to use the Environment Variables settings in the Windows environment. The setup in conf\hadoop-env.sh did not work for me. And I also set the $PATH variable to point to the Java/jdk1_..../bin folder in the Windows environment.The reason is that the javac and java commands running in cygwin are actually based on the installed Java SDK in windows.

After the setup, my next issue was to run the WordCount example. There were two parts. Running the WordCount.jar seems to be easy and successful. However, when I tried to use javac to compile the source code of WordCount.java, I met many errors, such as "package cannot be found...".

After a few hours of struggle, I found out the answer. The issues come from the classpath for Windows environment (when you run the command in cygwin). Here is the final solution for running the javac command in cygwin bash.

javac -classpath "C:\cygwin\hadoop-0.20.2\hadoop-0.20.2-core.jar;C:\cygwin\hadoop-0.20.2\lib\commons-cli-1.2.jar" -d playground/classes playground/src/WordCount.java

Another thing to remember is to make sure that you do have java 1.6.x installed in Windows (at the time of this note, the hadoop version I used is 0.20.2). Anything in Java1.7 will not work.

Thanks to the many discussion about this issue even though they did not give direct help but just some ideas. Hopefully this note can be helpful for someone else on the same road :)

Does SCRUM fit for data warehousing?

2011-04-04T22:36:00.001+02:00

There have been a broad discussion on how agile concept fits into the landscape of data warehousing. Considering the very special nature (well, many things are special, as I agree) of data warehousing, concepts like SCRUM seem to be beneficial for running projects in the data warehousing context. However, can all data warehousing activities fit into a sprint?

Well, I do agree that there are ways to break-down the activities into smaller steps so that they fit into sprints. However, the different life cycle statuses of a data warehouse may imply different tactics of implementing SCRUM concept.

When you work with a matured platform, where more than 80% percent of the major data subject areas, such as people, organization, employee, customer, products & services, have been populated in DW and the need to adding new data sources has been disappearing over the last 2 years, it is time to consider using SCRUM to manage the control the development activities around the DW. Why? Because the data model, the ETL, and the different rules, guidelines are getting matured. People are used to the way that things are supposed to be. So it is very easy to estimate what activities should fit into each sprint.

If we are in a situation where less than 20% of the data warehouse data is populated from source systems, it seems very challenging to consider using SCRUM. In such a status, the DW team are still struggling on the rules and ways of working. Substantial re-works are appearing on weekly or daily basis. In such a case, trying out any agile methods can be risky unless the SCRUM team has all key technical developers enrolled and gets the full management support (in case of re-works).

What if you are in-between these two states (21%-79% of data is populated)? I would be very careful with what has been populated in DW. If the majority of the enterprise master data, such as Customer, Products, Organization, Arrangements, has been ready, it is safe to consider SCRUM by including key technical developers during the process. Otherwise, consider a more classical DW approach.

By the way, this book may look interesting to read.

Is data valuable for an enterprise?

2011-04-02T22:29:00.000+02:00

There is a recent post on Information Management where people questioned and debated about the value of data itself to an organization.

As a general assumption, most enterprises value data as "valuable asset" for themselves. Whatever is kept in their IT systems is useful for creating business benefits. However, people always have to put data into certain context, such as a business process like "sales," "marketing," and "credit profiling", in order to create the value based on these data. On that sense, the data itself does not seem to be a valuable asset until the moment it is dragged into the context.

Well, this is an interesting observation or argument. But I would definitely question what is "data" to an enterprise. When a customer record is written in the database table at the IT system, this piece of data is already put into a context, i.e., the business definition of a customer to this business organization, and the business rules applied to this customer. And since this moment, this piece of data is creating value for the business. How? There can be few reasons.

1. The customer record is in fact the hard evidence that a business transaction happened. This transaction information lets the business organization have a legal way to protect its business. When a customer comes and ask for a refund or modification of certain product or services, the data kept in the system is a legal protection of what should or should not be considered. One could argue that I am using a certain context in this example. However, I would then ask when and how can you find any piece of data which has nothing to do with the business on any context? If that happens, why would you need data into the IT system?

2. In certain business domains, such as banking and health-care, keeping the data (I mean archiving) is a necessary need from a regulatory and compliance perspective. If an organization in these domains cannot fulfill this requirement, its business has to stop.

So, data in an enterprise is definitely a valuable asset. Why? Because you will lose your business if you do not keep data.

What is "One Version of Truth” for a Data Warehouse

2011-04-01T22:37:00.000+02:00

One version of truth or single version of truth (svot) has been a popular vision for data warehouse developers in many years. Large organizations tend to put one version of truth as a major milestone in the implementation of a centralized data warehouse. So, what does "one version of truth" mean? Is it achievable?

First of all, one version of truth means that all enterprise data are consolidated into a single data warehouse. The data is kept in a consistent and non-redundant way such that all data coming out from the data warehouse should be understood as the enterprise's common view of information. For example, if there are minor differences in the organization hierarchy data in different business area, the data from the the data warehouse should be considered as the correct, commonly-accepted, and enterprise-wide agreed organization hierarchy.

In many cases, the interpretation of single version of truth is extended so that one can have a "federated" view of different versions of truth in the single data warehouse. This implies that the data warehouse tends to give the governance of certain business logic away in order to maintain the view of “centralization" in a technical level. I think Malcolm Chrisholm's article "there is no single version of the truth" is in fact quite valid in the real data warehouse world. The only way to achieve a "single version of the truth" is to have the agreement and governance process on the business level.

However, when you manage to let all business to agree on almost everything of the organization's data, are we losing the power of being different and being able to think out of the box? The basic nature of a successful business is that people try to work out innovative ways and new concepts or new views of old things.

In my point of view, one version of truth is achievable in certain sectors, such as the military section and the public sector. In an enterprise where it is important to make business innovations and improvements, one version of truth sounds more like a thing in the road map... ...

SharePoint, Google Sites, and Enterprise 2.0

2011-03-31T22:16:00.000+02:00

After a while of struggling, I have to admit that SharePoint is going to take the lead in Enterprise 2.0 world. The challenges are not just about having a good collaboration platform, but a platform that integrates email, IM, calendar&schedule, and, most important of all, business intelligence. By the way, I would expect something more promising when Windows Mobile is tightly integrated with SharePoint.... The future enterprises will go mobile and MS is definitely the closest one to provide a fully-integrated solution.

On that sense, Google is the runner-up in the game at this moment. I believe that Google will take on the Cloud-computing as its main architecture. The mind-set or belief of enterprise users will basically set the business future for Google in this market. And I am not quite optimistic for this. Only SMEs and private users have the courage to take on the challenges. An incident such as information leakage can basically kill the belief of cloud computing technology in the market.

Where is IBM? Well, let's cross our fingers and see when Lotus Notes'time is completely over. I do believe that IBM is not able to take on the game if they do not have a serious re-consideration of their extensive use of java.

Any one else in the game? Well, we constantly see so many different tool-sets coming up (especially in the BI world :) that brings forward an organization in certain perspective. But everyone will soon find out that the cost of maintaining these different tools and their integrations will basically makes up their mind to migrate to a "single platform" strategy.

Code Canvas improves knowledge transfer in codes and models

2011-01-19T14:19:00.000+01:00

Software developers, programmers, and architects share the same habit of diagramming on white-boards. Lots of actual codes are developed based on the ideas implied in these diagrams or graphs. In fact, these visual elements become an essential part when the code or system is going to be made available to other people, users, new developers, customers, etc. Traditionally, software development involves the modelling step before and around the coding in order to capture the meta-knowledge. How about some efforts to make these steps easier and faster?

The Code Canvas prototype in MS research lab shows an interesting step toward an automation of creating code maps which eases the understanding of complex code structures and class diagrams. As shown in Kael Rowan's blog entry (here), the Code Canvas seems to be a future add-on to Visual Studio. As mentioned in an article at ACM COMMUNICATION ("Software Development with Code Maps," Vol. 53, No. 08, 2010), having a decent code map makes it much faster for newcomers to adapt into the project and complete their tasks.

The dilemma of consistency in technology architecture

2010-12-31T16:08:00.001+01:00

As one of the key elements of enterprise architecture, the "platform" or "technology" architecture is about what kind of software and hardware tools and systems to use across the whole organization.

One can imagine that there are ways of maintaining a strategy of having a "buying list" of these systems for an enterprise. Historically or even nowadays, large enterprises tend to put a lot of efforts on maintaining the "open system" concept. Here the "open system" concept means that an enterprise use software systems that accepts and enables interoperability, portability and open software standards. But in reality, not all software vendors give full-support to open standards and using systems from various technology and vendor background means extra cost of ownership to the IT managers. Thus, the following two phenomenas show the general dilemma of most enterprises towards technology choices.

Due to business and economical requirements, different departments are becoming more and more vendor dependent in spite of the enterprise-wide strategy of supporting open system concept.
Although most departments are using standard products, no matter if they come from a single vendor a or limit group of them, different local units still choose to use packaged solutions (often called appliances) in situations where they often find it hard (or expensive) to use the standard tools.

So should an architect put all her/his effort to ensure the consistency of the usage of standard tools?

Although consistency is definitely a personal virtue, it is absolutely not expandable to IT architecture. Instead of wasting all the effort to keeping the consistency, it is more useful to focus on maintaining the architectural strategy in a timely manner and getting ready to accept new ideas and changes from time to time.

In terms of integration of different systems, it is always important to consider a set of protocols in the infrastructure when adding new items in the buying list.

Middlewares and gateways that enable the integration;
Communication protocols such as web services
Information brokers, such as those that transform the data-types, char-sets (ASCII to EBCDC), or XML-based transformation
BPM tools that cope with processes at various frequencies;
Event and alert management tools;
Message-based systems, i.e., solutions that can keep messages for various systems.
Application-oriented adapters that support integration of applications with other existing solutions.

OO, star-schema, and anchor modelling

2010-10-22T22:17:00.000+02:00

Apparently there are many other data modelling methodologies besides relational modelling. In the data warehouse and BI world, the key word "multi-dimensional model" has been overwhelming for more than 2 decades. Kimball's theory on creating dimensional models has been well adopted into the industry. What we have found out, based on many people's (hard) experiences, is that the multi-dimensional data models are quite fit for data analysis purposes and it fits into the analytical mind of most business world. But, it is not a proper model for maintaining a large data warehouse where multiple data sources are ETL-ed into a single-version-of-truth.

The object-oriented modelling has been invented into the database world more than 10 years ago. As it is now, I can only see that Oracle has adopted some parts of this methodology into its commercial product. Maybe OO is just not the right way for managing the data, at least in the OLTP/OLAP world.

In the data warehouse modelling world, one of the key challenges to every data warehouse, is how to keep the history of data. Different data has different profiles. Some changes often, some needs to have a traceable history of the changes, some never changes, some only needs the most current change. To model the different enterprise data and keep the histories in a good manner, the concept of anchor modelling has just been discussed in the last ER conferences. It will be valuable to take a read into this topic and get to know more details about the method.