Changes

I created a presentation extoling the virtues of “decoupling” the data gathering function from the reporting function (Decoupling Benefits.ppt).  The idea needed to be combined with other solutions in order to meet the local need.  But, it seemed like a move in the right direction with respect to improving the culture.

I created a presentation extoling the virtues of “decoupling” the data gathering function from the reporting function (Decoupling Benefits.ppt).  The idea needed to be combined with other solutions in order to meet the local need.  But, it seemed like a move in the right direction with respect to improving the culture.

I began a search for a repository capable of providing the data integrity required by the Performance Measurement System.  I met with the Data Hub team to gather more information about their goals, standards and views on Protected “B” storage issues and future solutions.  They could not provide much information about the facilities which would be in place in the future I was attempting to move towards in any of our solution designs.

I began a search for a repository capable of providing the data integrity required by the Performance Measurement System.  I met with the Data Hub team to gather more information about their goals, standards and views on Protected “B” storage issues and future solutions (Monthly Reports.xlsx).  They could not provide much information about the facilities which would be in place in the future I was attempting to move towards in any of our solution designs.

I explored the availability of Protected “B” storage options in PHAC.  I found that decisions had not been made about facilities yet.  I explored cloud options by reviewing Treasury Board bids and found there was a bid in progress that would define a group of providers and services to be delivered. This implied that these cloud services in the future would be compliant easing our improvement design concerns about privacy.

I explored the availability of Protected “B” storage options in PHAC.  I found that decisions had not been made about facilities yet.  I explored cloud options by reviewing Treasury Board bids and found there was a bid in progress that would define a group of providers and services to be delivered. This implied that these cloud services in the future would be compliant easing our improvement design concerns about privacy.

I began to gather data to test this theory.

I began to gather data to test this theory.

I experimented with code which could be used to import data sent in email directly into a database table. The code works nicely importing data as it arrives at the proposed CCDIC.DataInput@canada.ca address, but, needs to be refined for the various body content expected.

I experimented with code which could be used to import data sent in email directly into a database table. The code works nicely importing data as it arrives at the proposed CCDIC.DataInput@canada.ca address, but, needs to be refined for the various body content expected. (Excelimportemail.doc)

January

January

In January, I began to develop a repository for incoming data, as a communal work area for reports under construction, local data, etc.  The goal was to create a working environment which was a “preview” of the groupware the Team will use in the future.

In January, I began to develop a repository for incoming data, as a communal work area for reports under construction, local data, etc.  The goal was to create a working environment which was a “preview” of the groupware the Team will use in the future.

I built the tool as an Excel Workbook with the sharing function turned on.  Right away, I began to find that the sharing feature was flakey. First, there were unadvertised constraints like a prohibition on the use of tables.  That is a huge loss – pivot tables are really the only way of “creating” reports other than creating a separate data page and mapping values to cells in that sheet in order to distribute data on a page like a report.

I built the tool as an Excel Workbook with the sharing function turned on.  Right away, I began to find that the sharing feature was flakey. First, there were unadvertised constraints like a prohibition on the use of tables.  That is a huge loss – pivot tables are really the only way of “creating” reports in Excel other than creating a separate data page and mapping values to cells in that sheet in order to distribute data on a page like a report.

Furthermore, the workbook behaved in unexpected ways (halting, freezing or closing the workbook right in front of you) as the number of users increased.  The idea will have to wait for the new tools in PHAC’s future.

Furthermore, the workbook behaved in unexpected ways (halting, freezing or closing the workbook right in front of you) as the number of users increased.  The idea will have to wait for the new tools in PHAC’s future.

Now, I needed to socialize the data models I had been capturing with the Team and introduce them to the common planning paradigm and the value of our local data to our work. I planned to deliver a new form built in Excel to demonstrate that a good user experience is possible in spreadsheets.  The form was capable of sending the data it collected to any drive. (Data Maodeling.xlsx, PPMG CCDIC planning tool.pptx)  

Now, I needed to socialize the data models I had been capturing with the Team and introduce them to the common planning paradigm and the value of our local data to our work. I planned to deliver a new form built in Excel to demonstrate that a good user experience is possible in spreadsheets.  The form was capable of sending the data it collected to any drive. (Data Maodeling.xlsx, PPMG CCDIC planning tool.pptx)  

The transmission idea makes it possible for the data provider to transmit and the data gatherer to receive data in an asynchronous manner.  This opens the door to continuous reporting and it effectively decouples data entry from collection.  Data is entered when environmental conditions dictate, not to meet the timelines of the reporting authority.

The data transmission idea makes it possible for the data provider to transmit and the data gatherer to receive data in an asynchronous manner.  This opens the door to continuous reporting and it effectively decouples data entry from collection.  Data is entered when environmental conditions dictate, not to meet the timelines of the reporting authority.

I met with the TB Folks to plan the creation of a repository (Worksheet) capable of holding incoming indicator data for reports. Regrettably, the pandemic intervened.

I met with the TB Folks to plan the creation of a repository (Worksheet) capable of holding incoming indicator data for reports. Regrettably, the pandemic intervened on our plans for March.

February

February

I refined the PMR data input form to include editing records and added a professional services and resources data input sections.  

I refined the PMR data input form to include editing records and added a professional services and resources data input sections.  

I had an important experience helping out in the effort to sort out financials records in order to enable the cuts requested.  I imported spreadsheet data into a database in order to perform reporting that was difficult in Excel.  I also explored the Power Pivot feature.  My hopes that this Excel feature would be more stable than the standard pivot table tool, were undermined by our continuing negative experiences.  A search on the net of Excel issues and power pivot help sites produced hundreds of stories from individuals like us, who felt the software was a bit shaky when you push it too far.  The trouble is that you will lose work and repeatedly return to step one of your last backup of the data.  This means that you must keep backups of each stage of your effort to develop the data.  Analysts are able to build a path or strategy of data development in their minds, but are disappointed when Excel “lets them down” as they attempt to follow their strategy. This is one of the reasons groups move to SAS or other proven applications for this kind of sophisticated data manipulation.

I had an important experience helping out in the effort to sort out financials records in order to enable the cuts requested.  I imported spreadsheet data into a database in order to perform reporting that was difficult in Excel.  I also explored the Power Pivot feature.  My hopes that this Excel feature would be more stable than the standard pivot table tool, were undermined by our continuing negative experiences.  A search on the net of Excel issues and power pivot help sites produced hundreds of stories from individuals like us, who felt the software was a bit shaky when you push it too far.  The trouble is that you will lose work and repeatedly return to step one of your last backup of the data.  This means that you must keep backups of each stage of your effort to develop the data.  Analysts are able to build a path or strategy of data development in their minds, but are disappointed when Excel “lets them down” as they attempt to follow their strategy. This is one of the reasons groups move to SAS or other proven applications for this kind of sophisticated post-Excel data manipulation.

I was asked to create a Publications SOP in a process diagram.  During the data gathering process, I participated in several discussions about blending the Publications process tracking with the current RBM reporting.  I explained what was possible.  In an attempt to organically expand the scope of the current RBM, I planted the seeds the CCDIC data collection model.  I explained that the content of RBM reports depend on the data in the collection not in the only form used to gather RBM data.  People tend to created data concepts from the existing tools – like data input forms.  A slide explaining the data model of the collection was enabling.  Ideas for data inclusion in the RBM reporting were quickly incorporated into the plan: MRAP commitments, Publications etc.(Publications SOP.pptx)

I was asked to create a Publications SOP in a process diagram.  During the data gathering process, I participated in several discussions about blending the Publications process tracking with the current RBM reporting.  I explained what was possible.  In an attempt to organically expand the scope of the current RBM, I planted the seeds the CCDIC data collection model.  I explained that the content of RBM reports depend on the data in the collection not in the only form used to gather RBM data.  People tend to create data concepts from the existing tools – like data input forms.  A slide explaining the data model of the collection was enabling.  Ideas for data inclusion in the RBM reporting were quickly incorporated into the plan: MRAP commitments, Publications etc.(Publications SOP.pptx)

In the email study, I assessed the value of an attachments management user story.  The concept was to drop attachments into a common repository in CCDIC rather than “attach” them to email or use common drive space with its confusing directory structure.  The calculated impact on email volumes seemed impressive but I did not have estimates of the total volume of email in CCDIC.   I created the beginnings of a database in the Access Repository for this purpose.  

In the email study, I assessed the value of an attachments management user story.  The concept was to drop attachments into a common repository in CCDIC rather than “attach” them to email or use common drive space with its confusing directory structure.  The calculated impact on email volumes seemed impressive but I did not have estimates of the total volume of email in CCDIC.   I created the beginnings of a database in the Access Repository for this purpose.  

Typically, “out-of-the-box” systems which meet a need initially, will attract attention and collaborators with similar requirements.  Then, a period of improvement begins.  Requests for improvements fall into two classes: i) those that can be implemented using “Out-of-the-Box” capabilities, and, ii) those that cannot be implemented without configuration (using VB code).  Now there are some requests that cannot be met.  Those requests begin to accumulate with no sign of solution.  This is a type of developmental paralysis.

Typically, “out-of-the-box” systems which meet a need initially, will attract attention and collaborators with similar requirements.  Then, a period of improvement begins.  Requests for improvements fall into two classes: i) those that can be implemented using “Out-of-the-Box” capabilities, and, ii) those that cannot be implemented without configuration (using VB code).  Now there are some requests that cannot be met.  Those requests begin to accumulate with no sign of solution.  This is a type of developmental paralysis.

In the meantime, those who volunteered this overlay of time and effort in order to achieve the current result can make an heroic effort to achieve what is asked of them, however, they will discover that they do not have the skills as well as performing tasks outside of their current job description and classification.

In the meantime, those who volunteered this overlay of time and effort in order to achieve the current result can make an heroic effort to achieve what is asked of them, however, they will discover that they do not have the skills and they would be required to perform tasks outside of their current job description and classification.

Most of the solutions I contrived for the issues in the User Stories, were achieved by using Visual Basic and/or database technology to configure or attempt to “shape the application to fit the business”.   Excel and Access, both Windows Office products, are sold as and widely recognized as “Configurable”.

Most of the solutions I contrived for the issues in the User Stories, were achieved by using Visual Basic and/or database technology to configure or attempt to “shape the application to fit the business”.   Excel and Access, both Windows Office products, are sold as and widely recognized as “Configurable”.

This scenario can be understood further using maturity modeling.  In short, the paralysis of the current situation will persist until CCDIC brings the capability to address the demands of the next level of system maturity. IE. those improvements not yet implemented due to the paralysis and more.

This scenario can be understood further using maturity modeling.  In short, the paralysis of the current situation will persist until CCDIC brings the capability to address the demands of the next level of system maturity. IE. those improvements not yet implemented due to the paralysis and more.

These local systems have achieved a maturity level that requires configuration solutions.   Out-of-the-Box solutions are appropriate for basic systems to which the business has typically conformed.  

These local systems have achieved a maturity level that requires configuration solutions.   Out-of-the-Box solutions are appropriate for basic systems to which the business has typically conformed.  Solutions based on configurations begin the process of shaping the application to meet the needs or fit the business.

Currently, the cultural attitude towards spreadsheets is, “Not another spreadsheet!”.

Currently, the cultural attitude towards spreadsheets is, “Not another spreadsheet!”.

This suggests that there is probably one negative characteristic that most do not enjoy.  Typically, this attitude is driven by the “Out-of-the-Box” user experience. And this negative experience is triggered by every spreadsheet.  If more than about a dozen inputs are required by the spreadsheet, the degree of negativity begins to grow disproportionally.  Symptoms include late input, incomplete inputs, disproportionate resistance to adding one new data item as if the sheet were at a “tipping point”, procrastination and ever-present questions like, “What are we doing this for?”

This suggests that there is probably one negative characteristic that most do not enjoy.  Typically, this attitude is driven by the “Out-of-the-Box” user experience. And this negative experience is triggered by every spreadsheet.  If more than a dozen inputs are required by the spreadsheet, the degree of negativity begins to grow disproportionally.  Symptoms include late input, incomplete inputs, disproportionate resistance to adding one new data item as if the sheet were at a “tipping point”, procrastination and ever-present questions like, “What are we doing this for?”.

To correct this attitude, a breakthrough is required in user experience.  Not an incremental improvement, but, a breakthrough likely to bring unexpected benefits as well as addressing the issue.  I felt I had such a breakthrough in ActiveX Object embedded and Visual Basic managed forms.  The forms have had an instant approval reaction from everyone who has seen them. These single record based forms are a full screen design using color to designate groups of data, employing drop down menus wherever possible and sensible validation that will turn red the background of any data item entered without respecting the input rules. There is navigation and record editing as well.

To correct this attitude, a breakthrough is required in user experience.  Not an incremental improvement, but, a breakthrough likely to bring unexpected benefits as well as addressing the issue.  I felt I had such a breakthrough in ActiveX Object embedded and Visual Basic managed forms.  The forms have had an instant approval reaction from everyone who has seen them. These single record based forms are a full screen design using color to designate groups of data, employing drop down menus wherever possible and sensible validation that will turn red the background of any data item entered without respecting the input rules. There is navigation and record editing as well.

I was quite confident we could perform a trial of the form in March, however, the pandemic intervened. But the form is ready to go and is documented.  Similarly, I have created forms for Publications and for Travel information. These forms can be used on any PC as the forms sheet to a data sheet in a Workbook.  The data input can be saved to any drive location in an automated fashion using the file type that meets the requirements of the receiving workbook or database.

I was quite confident we could perform a trial of the form in March, however, the pandemic intervened. But the form is ready to go and is documented.  Similarly, I have created forms for Publications and for Travel information. These forms can be used on any PC as the forms sheet to a data sheet in a Workbook.  The data input can be saved to any drive location in an automated fashion using the file type that meets the requirements of the receiving workbook or database.

So the form breakthrough pushes back on the negativity, but, more importantly, it is badly needed by at least 50% of the spreadsheet initiatives in the Agency.  Furthermore, it is a method of providing an Excel data-gathering and data-transmitting front-end to a database system in an organization in which database licences are rarely granted.  This is an intermediate solution that will stand up well until new tools are procured which deliver database technology to each machine meaning input forms will be created by the database, sent anywhere, filled in by users and the data gathered transparently. (PMRwithForm5.xlsm. PublicationsPlanningForm3.xlsm, UXForms.xlsm (a generic, use it to build any form, do not have to update code when a field is added)

So the form breakthrough pushes back on the negativity, but, more importantly, it is badly needed by at least 50% of the spreadsheet initiatives in the Agency.  Furthermore, it is a method of providing an Excel data-gathering and data-transmitting front-end to a database system in an organization in which database licences are rarely granted.  This is an intermediate solution that will stand up well until new tools are procured which deliver database technology to each machine meaning input forms will be created by the database, sent anywhere, filled in by users and the data gathered transparently. (PMRwithForm5.xlsm. PublicationsPlanningForm3.xlsm).

 

I have also created a "seed" form which users can use to build forms to "front-end" any spreadsheet "system" gathering data.  UXForms.xlsm is completely generic and expandable.  You do not have to update VBA code when a field is added or for any other reason.

.

.