A visit to the Alexandra Township

Today, on a training course, we were taken to the Alexandra Township (Alex) in Johannesburg. This was related to exploring diversity and sustainable development. It was an interesting and informative experience whereby we had the opportunity to sense and explore the businesses and living standards in the Township.


Firstly, if you are reading this blog entry you are likely to be privileged. Although they now have electricity and water, this was not the case for many years. Furthermore, in the really poor shacks, there is very little space, no water and often just a single light. The shacks are connected to one another, and there are small passages through which people can walk (you sometimes have to bend down, and squeeze aside if someone is walking the opposite way). Nonetheless, there is an atmosphere of positivity and community in the township.


With South-Africa having one of the largest gaps between rich and poor people, it was interesting to see that this is also evident in the Township. We visited a hosting establishment which provides light meals, drinks entertainment and a B&B facility. We heard the tough time which the owner had experienced, which gave us an understanding of the challenges and constraints these businesses have to survive.


We had a nice lunch, and some interesting discussions. Overall the day was very meaningful. I have developed a lot of respect for these people who have managed to build successful businesses in this environment.


We should all do our bit to understand differences and do something to help uplift and support the development of these communities. You are only limited by your imagination and the opportunities are out there!

Definition for Master Data Management (MDM) and Meta-data (metadata)

These definitions are my own personal consolidation, interpretation and understanding of all the definitions that I researched and found (see Bibliographies below). These definitions are hence copyrighted and you may use them as long as you give me, and this site - the appropriate credit for compiling them. You agree to do this also to protect the credit of the original authors of these definitions.

Master Data Management (MDM) is a set of disciplines, processes, tools, and solutions used to ensure consistency, completeness, control and accuracy of Master Data. MDM impacts the creation, capturing, storage, synchronization and maintenance of the organization’s Master Data for all stakeholders across the organization. The Master Data is defined as the cure business and reference data (structured or unstructured) and may include items such as: products, customers, vendors, data warehouse information, and content repositories. MDM spans across different applications, systems and databases, business processes, functional areas, geographies and channels.

Bibliography:
1. Fisher T., (MDM)—creating a single, unified view of an organization, cio.com, 30 April 2007
2. Druker D., Rich R., Master Data Management, ibmdatabasemag.com, July/2005.
3. Wikipedia, MDM, Last accessed 19 May 2009.
4. evancarmichael.com, The Importance of Master Data Management, Last accessed 19 May 2009.
5. Powell Media LLC. (2006). Master Data Management: Creating a single view of the business, 2006.
6. http://www.mdmsource.com/master-data-management-defined.html, May 2009:
a. Jim Whyte, Dow Chemical
b. Gartner
c. Colin White, BI Research
d. Dan Power, Hub Solution Designs
e. Steve Delien, Accenture
7. Smith A.M. (Ph.D), Enterprise Information Overview, ewsolutions.com, November 2008.

Meta-Data is a structured definition or description of data. It describes information such as content, quality, condition that characterizes a set of measurements or records. It may include individual datum, content item, or a collection of data including multiple content items and hierarchical levels. A database schema is a good example of metadata which describes for each data-field - its size, name, type, etc. Metadata may also describe the environment of the data which may include: how recent the data is; where it originates from, what the data is used for etc.

Bibliography:

1. Dictionary.com, Metadata, Last accessed May 2009.
2. The university of Queensland, An Introduction to Metadata, http://www.library.uq.edu.au/ iad/ctmeta4.html, 29 July 2003.
3. Wekepedia.org, Metadata, Last accessed May 2009.
4. Health informatics in New Zeland, Metadata Repositories in Health Care, http://www.hinz.org.nz/journal-pdf/995, Last accessed May 2009.
5. Climate Variability And Predictability, CLIVAR Data Policy, http://www.clivar.org/data/
data_policy.php, Last accessed May 2009.
6. Smith A.M. (Ph.D), Enterprise Information Overview, ewsolutions.com, November 2008.

Lessons learned

ADOBD "Connection Timeout" is in seconds NOT milliseconds. Always check your units !

The Undo for Excel VBA

VBA Macros in Excel clear the Undo stack. The stack is not easily accessible. The following link provides a Macro undo routine:

 

http://j-walk.com/ss/excel/tips/tip23.htm

 

This might be useful, but what about undoing previous actions when your VB "messes up the undo stack". The following code, which is loosely based on the link above - gives you a solution for that. It supports undo and redo, but has the following limitations:

 

1) You cannot undo formatting changes or cell-corner actions (double-click or drag).

2) It will only stacks a maximum of 1000 rows in a single change to avoid memory overflows.

3) To invoke a second undo action, you must at least change the cell selection.

 

I give you no warranty - use at own risk, but please give me and the original author credit for our work:

###################################################################################

Option Explicit
' Created by Gabriel Marcan (C) 2007

' I give you no warranty - use at own risk, but please give me and the original author credit for our work:

' Loosely based on http://j-walk.com/ss/excel/tips/tip23.htm
Public doList(10) As SavedRange
Public tempDo As SavedRange
Private isDo As Boolean
Public did As Boolean
Public bIndex As Integer
Public Const bLimit = 30
Public Type SavedRange
        Val As Variant
        isDo As Boolean
        RngName As String
        WsName As String
        WbName As String
End Type
Private Function ArraysEqual(a As Variant, b As Variant)
    On Error GoTo ReturnFalse
    Dim i As Integer
    Dim j As Integer
    If IsArray(a) And IsArray(b) Then
        For i = 1 To UBound(a, 1)
            For j = 1 To UBound(b, 2)
                If Not CStr(a(i, j)) = CStr(b(i, j)) Then GoTo ReturnFalse
            Next j
        Next i
        ArraysEqual = True
    Else
        If a = b Then ArraysEqual = True
    End If
    Exit Function
ReturnFalse:
    ArraysEqual = False
End Function
Sub MyDo()
     
    On Error GoTo NoDo
        If isDo Then MoveIndex
        If IsEmpty(doList(bIndex).Val) Then doList(bIndex).Val = ""
        did = True
        Dim rg As Range
        If doList(bIndex).WbName = "" Then Err.Raise 51, , "No History available"
        Set rg = Workbooks(doList(bIndex).WbName). _
                        Worksheets(doList(bIndex).WsName). _
                        Range(doList(bIndex).RngName)
        rg.Select
        If doList(bIndex).isDo = isDo Then Err.Raise 51, , "No History available"
        If ArraysEqual(rg.Value, doList(bIndex).Val) Then Err.Raise 51, , _
            "Cell corner actions (drag or double click) are not supported"
        rg.Formula = doList(bIndex).Val ' IsArray ubound
        If Not isDo Then MoveIndex
        did = False
       
        GoTo TheEnd
NoDo:
    Dim reun As String
    If isDo Then reun = "Re" Else reun = "Un"
   
    did = False
    If isDo Then
        isDo = False
        MoveIndex
    End If
   
    MsgBox "Cannot " + reun + "do. " + "More Details: " + Err.Description, , "Message"
    did = False
TheEnd:
        setupDo
End Sub
Public Sub Undo_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not did Then
        isDo = True
        tempDo.isDo = True
    End If
    If Not did Then MoveIndex
   
    doList(bIndex) = tempDo
   
    setupDo
End Sub
Public Sub MoveIndex()
    bIndex = bIndex + ((2 * Abs(CInt(isDo))) - 1)
    If (bIndex > bLimit) Then bIndex = 0
    If bIndex < 0 Then bIndex = bLimit
End Sub
Public Sub Undo_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    On Error GoTo SkipSub
    tempDo.WbName = Sh.Parent.Name
    tempDo.WsName = Sh.Name
    tempDo.RngName = Target.Address
    tempDo.isDo = isDo
    If (Target.Rows.Count > 1000) Then
        tempDo.RngName = _
             CStr(Target.Cells(1, 1).Address) + ":" + _
             CStr(Target.Cells.SpecialCells(xlCellTypeLastCell).Address)
    End If
    tempDo.Val = Range(tempDo.RngName).Formula
SkipSub:
    setupDo
End Sub
Private Sub setupDo()
Application.OnUndo "Undo Last action", "MyUndo"
Application.OnRepeat "Redo Last action", "MyRedo"
End Sub
Sub MyUndo()
isDo = False
MyDo
End Sub
Sub MyRedo()
isDo = True
MyDo
End Sub

##############################################################################

 

Feel free to comment, suggests improvements or send me your feedback on this code.

 

Spreadsheets' Auditing Solution

Spreadsheets' Auditing Solution

The solution is based on an add-in. Data changes are audited internally on a hidden spreadsheet and then synchronized to a database when the spreadsheet closes (if the connection is available).

The solution has the ability to prompt the user to integrate any new spreadsheet created (i.e. on-the-fly integration), but requires locking away the embedded VB code (automated naturally).

It is an efficient audit, that also promotes the use of external add-ins (which is better for reusability). It has virtually no impact on performance and requires at a minimum a database where the audit information will be to stored.

Tested on Office 2003 & 2007 successfully

My best PPC software (all free but 1)

Below is a list of the best tools I have come across for my Ipaq 6xxxx (CE 4). It is my personal favorites, which have become an integral part of my daily use of my Ipaq:

1. Total Commander - Absolute must: Free, zip and unzip files, built-in editor, edit registry, search function, other stuff.
http://www.ghisler.com/ce.htm

2. vBar: Free, switch between application, close applications, miniature memory and battery monitor.
http://vieka.com/vbar.htm

3. PHM tools: Free, I use mainly TrayLaunch for profile changing etc.
http://www.phm.lu/Products/PocketPC/PowerToys/

4. NetFront - Free trial, a significantly better alternative to pocket IE. Supports Java scripts, different browsing mode, in-page search, and 2 tabbed windows on the trail version.
http://www.access-company.com/products/netfrontmobile/browser/33_wm.html

5. Calc98 - Free, a good Scientific calculator. http://www.calculator.org/DownloadFile.html?filename=c9853a.exe
6. TCPMP - Free, Supports viewing of various images and video formats.
http://tcpmp.en.softonic.com/pocket

7. Pocket FreeMind - Free, fully compatible with FreeMind desktop files. Read, create and modify mind maps (I use the text version - 0.2) http://eringal.com/pocketfreemind/

8. TodayAgenda - Free, Enhanced calendar presentation on to the "Today" screen. http://www.pocketpcfreewares.com/en/index.php?soft=1475

9. Sun'n'Moon - Free, Shows rise and set times + moon phase on your "Today" screen.
http://pda.jasnapaka.com/sunmoon/releases/1.0.php

10. pRSSreader - Free, good RSS reader http://pda.jasnapaka.com/prssr/

11. Virtual Earth Mobile - Free, navigate roads / satellite image, supports goto lat/long. http://www.viavirtualearth.com/vve/Gallery/VEMobile.ashx

simple, hands-on example: pivot tables explained

This tutorial is short, but must be read word-for-word

 

Introduction:

--------------

This short tutorial aims to give you a simple hands-on example of what pivot tables are

Pivot tables allow you to report and analyze data.

It provides automatic filtering and grouping of data depending on the selections you make.

 

* Let us illustrate it with a gradual approach:

 

Our Data sample (note that the values for each COLUMN represent a single result, hence related):

--------------------------------------------------------------------------------------------------

VA       a          b          c          d

VB       e          e          f           g

VC       h          i           j           k

VD       l           m         m         n

VE       o          o          o          p

 

1. Single variable

 -------------------

1.1 For a single list of data, if there is no repetition of the data -> you can only get the same table

 

Pivot Table 1.1:

VA       a          b          c          d

TT        1          1          1          1

 

1.2 For a single list of data, with repetition -> you can get a summary (count) of all instances in the list

 

Pivot Table 1.2:

VC       e          f           g

TT        2          1          1<-- grouping count

 

2. Two variables

-------------------

2.1 If the data is unique for both sets -> you can get the list of the data or a matrix with a single entry in each cell

 

VA/VC            h          i           j           k

a                      1          0          0          0

b                      0          1          0          0

c                      0          0          1          0

d                      0          0          0          1

 

2.2 If one of the sets has repetitions -> you can get (besides using one variable only)

            2.2.1 a list grouping one list against the other (composite row / column list)

            count VA

            e          a          1

                        b          1

            ------------------

            f           c          1

            -------------------

            g          d          1

           

            or

           

            count VC

            o          l           1

                        m         2

            -------------------

            p          n          1

           

            2.2.2 a matrix showing the breakdown of the two variables in the same row (ie more than one entry in each cell)

           

            VB / VE           o          p

            e                      2          0

            f                       1          0

            g                      0          1

           

            2.2.3 a count of the variable for all instances of the other variable (group by the second)

            count VA

            VB

            e          2

            f           1

            g          1

           

3. Three or more

----------------

Expand the concept of grouping and filtering

 

* "Page" is a location for specifying overall filters on the variables

* You can also filter on the rows and columns selection

* Obviously, this methodology can get complex with large dimensions of data (refer to "Cubes")