If you are like my household and have invested in tonnes of technology over the years you don't like to keep replacing it every time a new idea comes out. We are quite heavily invested in Apple and have an iMac, Macbook Pro, 2 x iPads, 2 x iPhones, 2 x Apple Tv's, so you want them to all work seamlessly. When it came to printing from my iPhone all roads led to an AirPrint solutions which would require me replacing my 5 year old Canon Pixma iP6600D. This printer has served me well over the years and is hooked up to my iMac.
A bit of searching around and testing and the solution is an application from Netputing called HandyPrint. You download it and use it for 14 days before it requires an activation key which can be obtained by donating via PayPal.
Once installed it appears in System Preferences under "Other" and automatically picks up the printer attached to the iMac. Now I can print from any IOS device in the house directly to the printer.
- Paul Steynberg
Tuesday 2 October 2012
Sunday 4 September 2011
Correlate
The Google guys have been cooking up some very interesting stuff in the labs again. I took a look at the new trending service called Google Correlate today. Not entirely sure of its commercial value as an online free tool but it appears quite nifty.
One has to have a Google login in order to access the following site: http://www.google.com/trends/correlate but once in it is quite easy to navigate. One is able to trend web search data the Google has been collecting for many years. You can draw a line graph and the software will then match that profile to the closest profile it can find. One can also type in a web search string and it then finds the closest trend to that. For example, being in the Insurance industry, I typed in "Motor Vehicle Accidents" and the top 4 returned search trends are "vehicle accidents" (duh), "city planning", "questionnaires" and "battered women". The correlation between MVA's and battered women is strong (0.8744). I guess the big question is why? One is also able to upload a data file of time stamped data in order to find the closest correlation.
I guess one just needs to be mindful of the fact that these trends are not actual events but trends of search patterns. I would be interested in hearing from anybody who has used this technology in order to bring real life business use cases to life.
- Paul Steynberg
One has to have a Google login in order to access the following site: http://www.google.com/trends/correlate but once in it is quite easy to navigate. One is able to trend web search data the Google has been collecting for many years. You can draw a line graph and the software will then match that profile to the closest profile it can find. One can also type in a web search string and it then finds the closest trend to that. For example, being in the Insurance industry, I typed in "Motor Vehicle Accidents" and the top 4 returned search trends are "vehicle accidents" (duh), "city planning", "questionnaires" and "battered women". The correlation between MVA's and battered women is strong (0.8744). I guess the big question is why? One is also able to upload a data file of time stamped data in order to find the closest correlation.
I guess one just needs to be mindful of the fact that these trends are not actual events but trends of search patterns. I would be interested in hearing from anybody who has used this technology in order to bring real life business use cases to life.
- Paul Steynberg
Tuesday 23 August 2011
Controlled Use of Excel for BI
1. Introduction
In most Financial Institutions the use of Excel is so embedded that any project to curb this is almost doomed to failure from the start.
This blog is an attempt to elicit discussions and to explore the appetite to address some of the inherent risks with Excel spreadsheets and to see how one can deliver supportable MI using Excel. It should be stressed that this proposal should not be seen as a replacement for strategic tools or processes.
2. Benefits and Risks of Using Excel
The use of Excel comes with a multitude of pros and cons. In days past the downsides were considered to be worth the trade-off against the benefits but his is starting to change with the introduction of more stringent governance via legislation such as Solvency II, Basel II and SOX.
Below is a list of benefits and risks of using Excel in your core data streams.
2.1. Benefits
• The skills base for Excel is large and practically every person within a company will be familiar with it.
• Excel is relatively cheap and most users in an organization will have it.
• Excel lends itself to very easily creating financial models in a relatively short time span which normally could not be done in a more structured environment. Less reliance on IT means more flexibility and less time.
• The user experience from creating datasets to graphs is very intuitive and rich.
2.2. Risks
• Any VBA code or macros are specific to the workbook and are not held in a central repository to be reused. This raises concerns about version and change control over the code.
• If the required data set is sourced from any application/database within the IT estate, no way exists to document data lineage or do impact assessments when designing changes to these sub systems.
• Although Excel skills are widespread, most users will develop the model based on easy to develop rather than efficiency of code. No standard way of developing models makes it difficult to support/hand over.
• Any links to external data are also held in the workbook or specific to the PC. Often they contain T-SQL specific to the use-case and this is also not subject to version or change control.
• Excel has restrictions on the amount of data that it can consume. (1 million rows for Excel 2010, 2007 and 65,000 rows for Excel 2003)
• Security to the underlying database is not controlled via a centrally maintained application account but rather based on individual users. This requires users to be added and maintained at Database level.
• Access to spreadsheet models in not restricted via permissions or passwords as they are mostly stored in shared drives accessible to large groups of people.
• Users often save versions of the spreadsheet at various points during a process cycle and between process cycles for archiving. This leads to an explosion of redundant data and cuts of the code being held leading to increased storage costs. This can also lead to confusion as to what is the most recent and correct version. These issues are compounded by users sharing files by e-mailing them to each other.
• The models can become complex in nature and often process large volumes of data. These models can take a long time to run on a PC and in some cases can crash.
• Distributed development and locations lead to key person dependencies.
3. Risk Mitigation
In order to mitigate the risks outlined above one could put the following processes in place. Refer to Annexure A for the risk mitigation matrix.
• Set up standards on structure and development of Excel models.
• Ensure all models are developed against these standards, peer reviewed, tested, documented and appropriately transitioned into production.
• Store the models centrally in a controlled and identifiable location.
• Convert macros and VBA into add-ins and store them centrally.
• Implement security around the models for both access and changes.
• Implement appropriate change control and monitoring of the models to ensure that they are not changed without authorization.
In addition to the new or changed processes, technology can be used to mitigate some of the risks. Based on the risk matrix in Annexure B, a combination of Excel Services 2010 with PowerPivot and Prodiance, in theory could mitigate all the identified risks.
Below is a discussion around these technologies and how they could mitigate the risks.
4. Excel Services
Excel Services is the Excel engine delivered through Sharepoint and has the following key advantages:
• As it is delivered via Sharepoint on a server you can leverage the availability of larger memory, many more processors and 64 bit technology. This should reduce the time it takes to run larger and complex data models.
• Security is controlled via standard Sharepoint functionality.
• Centrally used data connections can be used thus eliminating the need for users to be granted direct access to the data.
• When published to Sharepoint one can only make certain parts of the model visible to the users thus hiding any business logic and underlying assumptions, enhancing security even further.
• Automatic scheduling of data updates can be implemented thus making refreshed reports instantly available to the users.
• As workbooks are versioned you can always restore to a previous one should you it be required.
Excel services does have some disadvantages listed below:
• Not everything that is native to the desktop version will work in Excel services such as add-ins and certain controls. Also some functionality becomes limited such as Pivot Tables and screen split and zoom functions.
• In order to modify the models you still need to pull them into the desktop version first and then republish them.
• Sharepoint is not a particularly easy technology to deploy and maintain and in order for Excel Services to be installed you have to have the Enterprise version.
5. PowerPivot
PowerPivot is an Excel add-in which has been developed by the Microsoft SQL Server Analysis Services Team and uses in-memory column compression technology branded as Vertipaq. This technology is very similar to that used by such market leaders as Qlikview. PowerPivot allows you to bring in large data sets into Excel and even join these sets to each other and then use the resulting data to report off. It is very efficient at consuming large amounts of data and when used in x64 bit mode with large amounts of memory is quite astonishing in its response.
PowerPivot also extends to Sharepoint and when combined with Excel Services in Sharepoint the uses and power suddenly start to become apparent.
6. Prodiance
In one of my blogs a while back I mentioned 4 companies that provide Excel Spreadsheet Control Software. We ultimately decided to back Prodiance as our choice, a decision that has now been ratified by Microsoft.
Up until 7th June 2011, Prodiance was an independent company specializing in risk and control software for Excel. It has now been purchased by Microsoft and is a wholly owned subsidiary. With Microsoft now purchasing the company more integration with the Office suite and Sharepoint is anticipated.
Advantages of implementing Prodiance over your spreadsheets are as follows (as per Prodiance pdf):
• Electronic sign-off and optional eSignatures
• Email notification of significant or unauthorized changes (e.g. exceptions, policy violations)
• Extensive cell-by-cell, file level and workflow audit trails
• Side-by-side comparison of changes between versions
• Management reports, dashboards and drill-down into detailed reports
• Automated document versioning
• Check-in/check-out (optional)
• Web based access to all historical versions
• Association of parent/child versions and unified view across all audit trails and reports
• Robust document security model integrated with Active Directory/LDAP users and groups
• Permissions to grant appropriate folder and document access levels (e.g. view, add, update, delete, approve, etc.).
• Microsoft Information Rights Management (IRM) encryption for spreadsheets containing sensitive information
• Workbook, worksheet and cell level protection
• Optional lock-down for cell level input control with data validation
• Excel Services for displaying and publishing read-only versions of critical spreadsheets and BI reports
• Extensive cell-by-cell, file level, system level and process level audit trails
• Tracking of changes to key inputs, outputs, spreadsheet data, formulas, macros and queries
• Tracking of changes to queries and data connections to external data sources
• Auditing support for spreadsheets and Access databases
• Optional lock-down for cell level input control with data validation
• Interactive link/dependency diagrams
• Auditing of specific input ranges (including named ranges)
• Automated email alerts upon changes to input ranges
• Color scheme tool to highlight (used and unused) input cells
• Spreadsheet validation and testing via automated cell, formula and range diagnostics
• Proactive identification of spreadsheet development and structural problems
• Comprehensive document and records management
• Enterprise class workflow management
7. Conclusion
In order to achieve the controlled use of Excel within an organization you will need to go through a change in technology, process and culture. I hope this blog has provided some readers with a spark for debate.
Appendix A
Appendix B
In most Financial Institutions the use of Excel is so embedded that any project to curb this is almost doomed to failure from the start.
This blog is an attempt to elicit discussions and to explore the appetite to address some of the inherent risks with Excel spreadsheets and to see how one can deliver supportable MI using Excel. It should be stressed that this proposal should not be seen as a replacement for strategic tools or processes.
The use of Excel comes with a multitude of pros and cons. In days past the downsides were considered to be worth the trade-off against the benefits but his is starting to change with the introduction of more stringent governance via legislation such as Solvency II, Basel II and SOX.
2.1. Benefits
• The skills base for Excel is large and practically every person within a company will be familiar with it.
• Excel is relatively cheap and most users in an organization will have it.
2.2. Risks
• Any VBA code or macros are specific to the workbook and are not held in a central repository to be reused. This raises concerns about version and change control over the code.
3. Risk Mitigation
In order to mitigate the risks outlined above one could put the following processes in place. Refer to Annexure A for the risk mitigation matrix.
In addition to the new or changed processes, technology can be used to mitigate some of the risks. Based on the risk matrix in Annexure B, a combination of Excel Services 2010 with PowerPivot and Prodiance, in theory could mitigate all the identified risks.
4. Excel Services
Excel Services is the Excel engine delivered through Sharepoint and has the following key advantages:
Excel services does have some disadvantages listed below:
5. PowerPivot
PowerPivot is an Excel add-in which has been developed by the Microsoft SQL Server Analysis Services Team and uses in-memory column compression technology branded as Vertipaq. This technology is very similar to that used by such market leaders as Qlikview. PowerPivot allows you to bring in large data sets into Excel and even join these sets to each other and then use the resulting data to report off. It is very efficient at consuming large amounts of data and when used in x64 bit mode with large amounts of memory is quite astonishing in its response.
PowerPivot also extends to Sharepoint and when combined with Excel Services in Sharepoint the uses and power suddenly start to become apparent.
6. Prodiance
In one of my blogs a while back I mentioned 4 companies that provide Excel Spreadsheet Control Software. We ultimately decided to back Prodiance as our choice, a decision that has now been ratified by Microsoft.
Up until 7th June 2011, Prodiance was an independent company specializing in risk and control software for Excel. It has now been purchased by Microsoft and is a wholly owned subsidiary. With Microsoft now purchasing the company more integration with the Office suite and Sharepoint is anticipated.
7. Conclusion
In order to achieve the controlled use of Excel within an organization you will need to go through a change in technology, process and culture. I hope this blog has provided some readers with a spark for debate.
Appendix B
Wednesday 27 July 2011
New Features in SQL Server Code-name Denali - CTP3
Microsoft are shipping some new technology in the upcoming version of SQL Server. This new technology is code-named "Apollo" and introduces two new features.
- Paul Steynberg
- Columnstore Indexes
- Vector-based query execution
- Paul Steynberg
Monday 25 July 2011
SQL Server Project "Crescent" Demo
Follow this link to see a demonstration of SQL Server Project "Crescent". To quote "a new immersive ad-hoc visualization tool that is part of SQL Server Code Name "Denali" Reporting Services. Project "Crescent" is designed with end users in mind to quickly, easily, and visually explore their data and answer ad-hoc questions in just a few clicks."
It sounds like exciting stuff and certainly plugs some gaps in the Microsoft stack.
- Paul Steynberg
It sounds like exciting stuff and certainly plugs some gaps in the Microsoft stack.
- Paul Steynberg
Saturday 18 June 2011
Paperless Home
The concept of going paperless both at home and at the office has been around for many years. The execution of this idea has not always been easy or successful. I have tried this in the past with flatbed scanners and various methods of storing the output for easy retrieval and indexing. To date my attempts have been somewhat failures. This is until now. A few weeks ago I found the perfect combination of scanner and software that make the entire process a pleasure.
The solution I am using is for the Mac so if you are PC based you may need to do some more research. My requirements were quite simple. The process of scanning should be effortless and the software should be easy to use. I started looking at Evernote but did not like the idea of all my documents being stored in the cloud, especially bank statements, copies of passports, certificates, mortgage statements etc. Evernote is free as a download but in order to get the most out of it you are required to sign up with them on a monthly or annual basis. It is not expensive, $5 per month or $45 per annum and is very handy for ensuring your documents are held in offsite storage for backups. The application is great but the security issue tipped the scales for me.
After a lot of research and testing out various products I finally decided on DevonThink. Similar concepts to Evernote but a once off fee ($49.99) for the product and you store the documents locally in a database. This would mean that I would have to make sure that I am backed up but the Mac does this automatically through Time Machine.
DevonThink is easy to use comes with some great features such as side bar tray to which you can just drag documents from anywhere and an add-in to Safari that allows you to capture the open web page directly.
I then turned by attention to document scanners and opted for the Fujitsu ScanSnap S1300. This great little scanner (280mm long and 100mm wide) allows you to scan up to 10 pages at a time and can do either 1 side or both sides of the document. The other great feature I found is the ability to set up profiles for scanning which then call DevonThink and drop the document into the inbox for categorisation. It also does its own OCR and orientates the pages based on character recognition. Once the document is in DevonThink you can then search within the scanned documents for key words.
Now when the post arrives or we get back from shopping we just put the invoices/documents through the scanner with one touch of the button and then categorise them later in DevonThink when we have some quiet time. In the mean time all those documents can be destroyed and no more filing.
- Paul Steynberg
The solution I am using is for the Mac so if you are PC based you may need to do some more research. My requirements were quite simple. The process of scanning should be effortless and the software should be easy to use. I started looking at Evernote but did not like the idea of all my documents being stored in the cloud, especially bank statements, copies of passports, certificates, mortgage statements etc. Evernote is free as a download but in order to get the most out of it you are required to sign up with them on a monthly or annual basis. It is not expensive, $5 per month or $45 per annum and is very handy for ensuring your documents are held in offsite storage for backups. The application is great but the security issue tipped the scales for me.
After a lot of research and testing out various products I finally decided on DevonThink. Similar concepts to Evernote but a once off fee ($49.99) for the product and you store the documents locally in a database. This would mean that I would have to make sure that I am backed up but the Mac does this automatically through Time Machine.
DevonThink is easy to use comes with some great features such as side bar tray to which you can just drag documents from anywhere and an add-in to Safari that allows you to capture the open web page directly.
I then turned by attention to document scanners and opted for the Fujitsu ScanSnap S1300. This great little scanner (280mm long and 100mm wide) allows you to scan up to 10 pages at a time and can do either 1 side or both sides of the document. The other great feature I found is the ability to set up profiles for scanning which then call DevonThink and drop the document into the inbox for categorisation. It also does its own OCR and orientates the pages based on character recognition. Once the document is in DevonThink you can then search within the scanned documents for key words.
Now when the post arrives or we get back from shopping we just put the invoices/documents through the scanner with one touch of the button and then categorise them later in DevonThink when we have some quiet time. In the mean time all those documents can be destroyed and no more filing.
- Paul Steynberg
Thursday 25 February 2010
iXBRL - LinkedIn Group
Hi Everyone
I recently attended a product sponsored session on iXBRL and HMRC/Companies House submissions for the UK. I will put together an updated paper on new developments this week but would like to just highlight that I have created a group on LinkedIn called "iXBRL in the UK". This group is specifically designed to address issues that companies are having with the submission of accounts in the format and to discuss not only the process being followed to comply but also a forum to discuss any software solutions that start to make themselves available. Just search for it on LinkedIn and I look forward to any contributions.
- Paul Steynberg
I recently attended a product sponsored session on iXBRL and HMRC/Companies House submissions for the UK. I will put together an updated paper on new developments this week but would like to just highlight that I have created a group on LinkedIn called "iXBRL in the UK". This group is specifically designed to address issues that companies are having with the submission of accounts in the format and to discuss not only the process being followed to comply but also a forum to discuss any software solutions that start to make themselves available. Just search for it on LinkedIn and I look forward to any contributions.
- Paul Steynberg
Tuesday 16 February 2010
PowerPivot Storage
For you hard core techies out there, stop reading now. This post is for those people in the world who had no idea that the Office 2007 (and upwards) files are actually just a bunch of files zipped up. I was curious to see how PowerPivot Data was stored with the Excel spreadsheet and was amazed to find out that a number of my associates were unaware of the make up of the new Excel formats.
In order to illustrate the concept take an Excel (or Word) 2007/2010 file and just to be safe make a copy of it. Then change the file extension from XLSX to ZIP and then open in Winzip. Shock and horror we find a whole bunch of files (mostly XML) in various directory structures. This was done in order to comply with Office Open XML Format. Read all about it here.
Back to the original question about how the PowerPivot Data is stored. It is stored in a directory called xl\customData with the extension DATA within the Excel (zip) file. Here is a bit more on how the data interacts with your memory.
- Paul Steynberg
In order to illustrate the concept take an Excel (or Word) 2007/2010 file and just to be safe make a copy of it. Then change the file extension from XLSX to ZIP and then open in Winzip. Shock and horror we find a whole bunch of files (mostly XML) in various directory structures. This was done in order to comply with Office Open XML Format. Read all about it here.
Back to the original question about how the PowerPivot Data is stored. It is stored in a directory called xl\customData with the extension DATA within the Excel (zip) file. Here is a bit more on how the data interacts with your memory.
- Paul Steynberg
Monday 1 February 2010
PowerPivot
Microsoft promised BI for the masses and PowerPivot is the first real taste of this promise. Originally code named Gemini this Excel Add-in was developed by the SQL Server Team. In a nutshell it is SQL Server Analysis Services on your PC. In order to get it up and running you have to have Office 2010 (Beta) installed and XP should be on SP3. Here is a full list of requirements.
I am not going into the full technical bits of the product as this has been done many times before and here is a full list of links to get you up to speed:
Take a look at the demo videos as they are quite quick and really show the power of the product. To illustrate I took a normalised data source and imported 3.5 million rows into PowerPivot in under 2 minutes. I then created a Pivot Table from the data with the new look and feel of having slicers across the top and down the left hand side. The results were blistering fast.
This is a first release and the product is full of bugs. As with most Microsoft products wait for V2 before releasing into your production environment. More to come later.
- Paul Steynberg
Monday 12 October 2009
iXBRL - Don't Panic
Introduction
Companies in the UK will have to submit financials to Her Majesty’s Revenue and Customs (HMRC) via an online process within the next 2 years. I had a session with Ernst & Young about the language to be used called XBRL. I have also attended a workshop at HMRC titled “Company Tax Returns and Online Filing”. This is a summary of my findings and my personal views on the impact.
Overview of XBRL
XBRL stands for eXtensible Business Reporting Language. It is one of a family of "XML" languages which is becoming a standard means of communicating information between businesses and on the internet.
Impact
- CT600 – Currently can be submitted via XML and will not change.
- Accounts – Must be submitted online in iXBRL format.
- Tax Computations – Must be submitted online in iXBRL format.
4 Taxonomies will be supported by HMRC but for the first 2 years of implementation only a limited subset of each taxonomy will be required.
| Taxonomy | Full Tag Set | Limited Tag Set |
| UK GAAP | 4375 | 1182 |
| UK IFRS | 3400 | <1600 |
| UK Common Data | 900 | 900 |
| CT Computational | 4548 | 1350 |
One is not restricted to the limited tag set but you are only required to at least submit these for the first 2 years. Thereafter the full tag set will be required.
The current versions of the taxonomies can be downloaded from this website.( http://www.xbrl.org/uk/Taxonomies/)
The Companies House and HMRC issued a joint statement on 1st September 2009 stating that they are working together and that both their filing services are being aligned so that a single point of filing can be used.
As with any technology solution we will find many ways of solving this challenge ranging from ERP Supplier based solutions right through to stand-alone file converters. Unfortunately iXBRL is not yet widely used so I have not yet managed to find anything worth taking a look at yet. HMRC is busy reviewing a number of products and certifying them but when pushed they did not want to yield any names or costs just in case they were seen to favour one supplier over another. They did assure us that a number of suppliers would be ready for market in Q1 of 2010.
Here is a list of companies who I believe are working on a solution. Do not take this as gospel, just through my googling.
Awareness of XBRL and its impact is at an acceptable level within the UK market. Based on the Q&A session with HMRC the first 2 years are really going to be a phased/teething process and companies will not be penalised or prosecuted if they don’t get it quite right. We also do not have any products to work with as they are still being developed.
I would suggest a measured approach to the online filing which starts with Finance obtaining the taxonomies that they intend to use for filing and to start manually mapping the figures across from the financials. This in my opinion is going to be the largest portion of the work required. A deadline of the mapping tables by end Q2 2010 should be achievable.
Get your IT Department to keep tabs on the development of the various alternatives in the software space and then organize demonstrations and evaluations as and when possible. I estimate that we should be able to find something by Q2 2010. Based on all the input I would at this point suggest that we look to purchasing an Office Add-In that will allow you to continue with our current method of producing the financials and thus reduce impact on the business.
Works Cited
1. XBRL International. What is XBRL. [Online] [Cited: October 9, 2009.] http://www.xbrl.org/WhatIsXBRL/.
- Paul Steynberg
Subscribe to:
Posts (Atom)