Recursive Creativity LLC

RECURSIVE CREATIVITY LLC

ABOUT ME

I have been involved with spreadsheets since VisiCalc and was very proficient with both VP Planner and Lotus 123. My experience in Finance, Accounting and Technology roles has developed my knowledge and appreciation for all that computers (especially Excel) can do.

In addition to my corporate experience, I have taught Accounting, Finance and computer classes at community colleges, private and public Universities. My teaching accolades included always having excellent evaluations and I was nominated numerous times as instructor having most positive influence on students.

My other interests include Business Intelligence, optimization of desktop and laptop operating system software and associated Freeware.


User Group Presentations


SQLSaturday


Excel @ SQL Reporting, SQLSaturday #107 - Houston, TX - April 21, 2012

This presentation will provide an overview of using Excel as a front end for reporting with SQL Server and SQL Server Analysis Services. Topics covered will include best practices for using VBA to execute queries using ADO and ADOMD and a discussion of queries that facilitate creating dashboards: STUFF, FOR XML PATH, CASE, PIVOT, and COALESCE. Other topics include, generating PivotTables and QueryTables, OLAP PivotTable Extensions, MS Query, and using VBA to segregate data in the Recordset generated using SQL. And last but not least all DBA’s favorite SQL, dynamic SQL...passing variables from Excel to generate user defined data.

Also covered will be some Excel tricks for overcoming error messages sometimes created by bad SQL, no data returned, and creating sheets on the fly while preventing dashboard equations from displaying errors.

Please note, given the hour limit for this presentation, much of the presentation will be an overview only. More specific details for some of the content can be found in other presentations or categories elsewhere on my website. Also, if you live in Houston, some of the topics may be presented in more detail at future Houston SQL Server User Group meetings.

If you do not have access to AdvertureWorks 2008, many of the equations in the files linked to cubes (SSAS) above will error out and functionality (buttons) that drive other processes will not work. Other files that use my custom billing data database or the stock data downloaded from InfoChimps will also have limited functionality. More details about the stock data can be found below in a previous presentation to the Houston SQL Server User Group.

  • Documentation of Files. This includes files used in the presentation, various VBA code, and SQL queries and stored procedures.

ADO and ADOMD

  • Some best practices will be discussed in the course of my presentation and are addressed in my PowerPoint file (yet to be posted). I am not going to write a book on VBA but rather provide a few tips on what I consider best practices. If you want more details, that is why Al Gore invented the Internet!
  1. One example of what not to do!

Dashboards and Reporting

  • Dashboard Queries - CASE, PIVOT, COALESCE, STUFF and FOR XML PATH will be covered.
  • Dynamic SQL - Passing variables from Excel data validation fields and forms to Stored Procedures and then returning data based upon those variables.
  • Excel equations to eliminate errors are will also be demonstrated. More specifically, how to create dashboard equations that will not error out if the underlying Excel data sheet is deleted.
  1. Stored Procedures and Miscellaneous SQL Queries (and some VBA) are embedded in file, Excel@SQL Reporting - SQL Documentation.pptx.
    See "Documentation of Files" link above.
  2. Dashboard Queries

Excel Tables

  • PivotTables and QueryTables, native and free form (equations). Also to be discussed are using each as backend for dashboards.
  • OLAP PivotTables Extensions: Add MDX Equations to Calculated Fields in PivotTables.
  1. Billing Dashboard
  2. PivotTables: OLAP Extensions and Equations

MS Query and Data Connections

  • Some basic uses of MS Query for reporting, generating PivotTables and DataTables, etc. will be presented.
  • Also discussed will be the various types of data connections.
  1. MS Query.zip

Recordset

  • Examples using the Recordset in VBA to filter the data generated via a SQL query will be presented.
  • Some examples of uses of the Recordset are also provided in Dashboards and Reporting section above. The above example uses AdventureWorks. The example below uses a stock quotes database I build from data downloaded from InfoChimps.
  1. Recordset.zip
  • Above file also includes alternative SQL solution. Using SQL queries to generate arrays for filtering data will be discussed.


All SQL Saturday 107 Files

If you do not have access to AdvertureWorks 2008, many of the equations in the files linked to cubes (SSAS) above will error out and functionality (buttons) that drive other processes will not work. Other files that use my custom billing data database or the stock data downloaded from InfoChimps will also have limited functionality.


Top of Page



Houston SQL Server User Group


Excel @ SQL Reporting, April 10, 2012

This presentation will provide an overview of using Excel as a front end for reporting with SQL Server and SQL Server Analysis Services. Topics covered will include best practices for using VBA to execute queries using ADO and ADOMD and a short discussion of queries that facilitate creating dashboards: STUFF, FOR XML PATH, CASE, PIVOT, and COALESCE. Other topics include generating PivotTables and DataTables, OLAP PivotTable Extensions, MS Query, and using VBA to segregate data in the Recordset generated using SQL. And last but not least all DBA’s favorite SQL, dynamic SQL....passing variables from Excel to generate user defined data sets.

Also covered will be some Excel tricks for overcoming error messages sometimes created by bad SQL, no data returned, and creating sheets on the fly while preventing dashboard equations from displaying errors.

This presentation I am presenting at SQL Saturday #107. Material is found above under SQL Saturday #107.


Top of Page




Excel Reporting from Cubes (SSAS), Overcoming some Design Limitations Post-Implementation, November 8, 2011

So, you have built your cube(s), or Unified Dimensional Models (UDM’s), and are now set to develop reports. However, you realize some of the dimensions include sensitive data or data that not everyone should be privy to. How can you limit user exposure to sensitive data from within Excel? This demo will show how to convert a PivotTable to a free-form report (equations) and limit user exposure to some data in SQL Server Analysis Services cubes simply using Excel equations.

Excel, Cubes Downloads:

The AdventureWorks files (databases and cube) I downloaded from CodePlex.com is the data source in the Excel file available in the compressed file below. The data source may not be available at a future date. Thus it can be downloaded here.
  1. AdventureWorksDW2008.zip

Using the Foreach File Enumerator (Container) in Integrated Services (SSIS), November 8, 2011

Do you have multiple billing records, sales batches, etc. that need to be imported into a database for reporting or analysis? Discussed will be how to set up an Integrated Services package to load a series of .csv files (comma delimited) into a SQL Server database (table). I had originally intended to develop a project to load daily stock quotes for the AMEX, NASDAQ and NYSE from 1970 through 2010. Stocks quotes from each exchange are in 26 separate files…one for each letter of the alphabet.

Because of time limitations, the solution demonstrates how to load data from monthly invoices (.csv files) for professional services rendered. The time to loop through these records takes seconds compared to the 15 minutes it takes to load the almost 20 million stock dividends and prices for each exchange. Below are links to my Stock Market project, the source files and discussion of some limitations in SSIS that I had to work around for my solution.

The limitations of SSIS I encountered and how I solved them using Stored Procedures are available in a separate document (link) below. There are a lot of incorrect demos and discussions on the Internet and even some texts do not cover enough of the basics or issues you will encounter to develop a working solution. This demo will walk through each step and provide enough documentation that you will walk away comfortable enough to add this topic to your knowledge base.

Foreach File Enumerator Downloads:

Billing Data
  1. Creating Foreach File Enumeration SSIS Project
  2. All Data Files
  3. BillingData SSIS Project, Demo
  4. Load BillingData SSIS Project, All Inclusive
  5. SQL Queries and Scripts
Stock Market Data
  1. Stock Dividends
  2. Stock Prices
  3. SQL Queries and Scripts

I was looking for large sets of data to play with so I could learn PowerPivot and stumbled across InfoChimps. It has a plethora of free downloads of large and small data sets. The data files above contain files I downloaded from InfoChimps and slightly modified. Furthermore, if you look at the SQL scripts, I added more data fields for doing time-series calculations and analysis. This will be an ever-evolving project and more fields and changes to underlying SQL downloads will change. I also hope to learn and develop some of the analysis using C#.NET.


Top of Page



Kansas City Office Geeks


Dynamic Reporting Using Excel and SQL Server, May 7, 2009

Discussion centered on dynamic Excel reporting utilizing SQL Server Stored Procedures, PivotTables, DataTables and utilizing each for creating Dashboards.

  • An overview of loading and extracting data via dynamic SQL and some best practices as it relates to Data Connections and VBA/ADO for Excel.
  • Formulas and many tips and tricks for creating dynamic reports…..reports with very little, if any, manual effort on a recurring basis.
  • VBA code provided for recursively refreshing DataTables, PivotTables, etc.
  • While VBA focused on Excel, code for refreshing Excel Linked Objects in Microsoft Word and Microsoft PowerPoint were also discussed.
SQL Server 2008 Downloads:
  1. SQL Server 2008 Files - All
  2. SQL Server 2008 Data Files
  3. SQL Server 2008 Reports
  4. SQL Server 2008 SQL Scripts
  5. SQL Server 2008 SSIS Files
Miscellaneous
  1. SQL Queries: 3 different methods (CASE, PIVOT, COALESCE), same results
  2. Creating Data Connections
  3. User Changes
  4. PowerPoint Deck

Top of Page



Kansas City SQL Server Users Group


SQL Server Analysis Services Add-in for Excel 2003, June 8, 2006

Presentation on the SQL Server Analysis Services (SSAS) Add-in for Excel 2003.

The demonstration centered on using the Indirect function in Excel to shield users from confidential data in SSAS cubes. Sometimes making changes to cubes is cost prohibitive. However, you can avoid making modifications to cubes so that data can still be private and shield users from viewing certain dimensions or measures by removing a PageBy field from PivotTable and creating a custom PageBy field.

The custom PageBy field is a data validation field that employs the Indirect function to direct which Dimension or Measure you want users to see. For example, you could build a budget cube showing all cost centers. You would then build spreadsheets with custom PageBy fields showing only the Cost Centers users should have access to.

SSAS Add-in for Excel 2003 Downloads:
  1. Details
  2. CLA Report Mockups.xls

Top of Page



SQL Server 2000 Skills Enhancement, February to July, 2005

In cooperation with Johnson County Community College, I organized a 6-month skills enhancement seminar for individuals wanting to learn all aspects of Microsoft SQL Server.

The classroom and all materials except participant texts were covered by sponsors.

I taught theory of SQL Server Design & Implementation class with colleague recruited to teach hands-on portion.

Top of Page



KC Web Pros


Web Sites, So Easy (and cheap) A Caveman Can Do It!, May 26, 2010

Top of Page



Windows & Freeware Users Group


Real Player SP, SyncToy; June 15, 2010

Demonstration covered some of the new features of Real Player SP which include downloading on-line videos (.flv format) and converting downloaded content (.wmv format).
Also discussed were backup strategies versus synchronization. Backup software is good for compression of files and is a feature of most operating systems. There are also some good programs that can be purchased. However, Synchronization, my preference, provides real-time access to your files if something goes wrong. Backup software often backs up your data to a file that is a proprietary format...you will only get to your files once you have restored your PC and installed whatever software you used.

There are many programs that can be purchased and many freeware programs available via the Internet. However, I have found SyncToy to have the options I prefer and far superior in functionality. What synchronization provides is real-time access to your files should something happen to your PC. I use the Echo method for synchronization. In essence, all folders structures and files, subsequent renames and deletes are copied from the source (your PC) to the backup device. The backup device can be another computer on the network or an external hard drive.

Should your laptop or desktop crash you have complete access to your files on your backup device. Please note, this only works if you backup regularly and you have access to another computer. I only make note of this because I lost a desktop hard drive, a laptop hard drive and laptop keyboard in a recent storm...could not access the external drive until at least one PC was up and running.

Another bonus of SyncToy is that any renamed or deleted files can be reviewed before final deletion because they are not deleted outright, they are put in the Recycle Bin. My backup strategy is to synchronize to an external hard drive daily several times a day. I also synchronize to another hard drive once a month and rotate with hard drive in my safe deposit box. All synchronization is automated using Task Scheduler. Setting up the synchronization through Task Scheduler is detailed under Help.

Top of Page



Windows Live, November 17, 2009

Provided demonstration on configuration options and usage of the Windows Live family of products:

  • Windows Live Call
  • Windows Live Mail
  • Windows Live Messenger
  • Windows Live Movie Maker
  • Windows Live Photo Gallery
  • Windows Live Writer

Top of Page



Microsoft Virtual PC 2007, May 19, 2009

Presented usage, configuration options and strategies for managing multiple Virtual Hard Drives (VHD's).


Top of Page



Co-founder, President; May, 2004 to September, 2005

Led monthly meetings on various topics related to Microsoft operating systems and associated freeware available via the Internet.

Top of Page



Education



New Horizons - Overland Park, KS - November 2009 to March 2010

  • SharePoint Designer, Windows SharePoint Services
  • Programming MS .NET Framework with Visual Studio 2005, Core Foundations of .NET 2.0 Development using C#
  • HTML, Cascading Style Sheets (CSS), and JavaScript

My interest in C#.NET was not to become a .NET Developer but hopefully work somewhere using SharePoint and had deployed or was considering deploying Excel Services. Visual Basic for Applications (VBA) in Excel does not function in SharePoint. C# has to be embedded for the objects such as buttons, checkboxes and combo boxes to work in Excel when published to SharePoint using Excel Services.

Top of Page



North Lake College - Irving, TX - March 1999 to October 1999

  • While studying for and passing 4 certification exams (Networking Essentials, Windows NT: desktop, server, enterprise) to earn the Microsoft Certified Professional (MCP) designation, I also taught Microsoft Access at North Lake College.

Top of Page



Illinois State University - Normal, IL

  • Master of Business Administration, August 1990
Supplemented graduate studies with additional undergraduate accounting classes.
  • Bachelor of Science, Finance major with Business Administration minor, May 1988

Top of Page



Teaching Experience

I have always had excellent evaluations from students and peer/administrative reviews.


North Lake College - Irving, TX


Adult Education - October, 1999
  • Microsoft Access, Level 1

Top of Page



Heartland Community College - Normal, IL


Department of Social and Business Sciences - August, 1992 to December, 1995
  • Financial Accounting
  • Managerial Accounting
Department of Computer Science, Microcomputers - March, 1992 to May, 1994
  • Introduction to Spreadsheets
  • Introduction to Microcomputers

Top of Page



Millikin University - Decatur, IL


Department of Finance - Spring Semesters, 1992-1995
  • Investments

Top of Page



Illinois Central College - East Peoria, IL


Department of Accounting - August, 1991 to May, 1992
  • Financial Accounting

Top of Page



Illinois State University - Normal, IL


Adjunct Faculty
Department of Finance, Insurance and Law - August, 1990 to December, 1991
  • Business Finance
  • Personal Finance

Department of Accounting - August, 1990 to May, 1991
  • Financial Accounting

Graduate Assistant
Department of Finance, Insurance and Law - August, 1988 to May, 1990
  • Business Finance

Recognized several semesters as ISU faculty member having most positive influence on students.

Top of Page