RECURSIVE CREATIVITY LLC
ABOUT ME
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
SQL Saturday
Excel @ SQL Reporting, SQL Saturday #107, Houston, TX
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 finally, 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 and are addressed in accompanying PowerPoint file.
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.
- 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. - 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.
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 various types of data connections.
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.
- Above file also includes alternative SQL solution. Using SQL queries to generate arrays for filtering data will be discussed.
If you do not have access to AdvertureWorks, 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.
Houston SQL Server User Group
Excel @ SQL Reporting
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 brief 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 finally, 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.
Excel Reporting from Cubes (SSAS), Overcoming some Design Limitations Post-Implementation
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.
Using the Foreach File Enumerator (Container) in Integrated Services (SSIS)
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
- Creating Foreach File Enumeration SSIS Project
- All Data Files
- BillingData SSIS Project, Demo
- Load BillingData SSIS Project, All Inclusive
- 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.
Kansas City Office Geeks (no longer active)
Dynamic Reporting Using Excel and SQL Server
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 Files - All
- SQL Server 2008 Data Files
- SQL Server 2008 Reports
- SQL Server 2008 SQL Scripts
- SQL Server 2008 SSIS Files
Kansas City SQL Server Users Group
SQL Server Analysis Services Add-in for Excel
Presentation on the SQL Server Analysis Services (SSAS) Add-in for Excel.
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 Downloads:SQL Server Skills Enhancement
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.
KC Web Pros
Windows & Freeware Users Group (no longer active)
Real Player SP, SyncToy
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. 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.
Windows Live
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
Microsoft Virtual PC 2007
Presented usage, configuration options and strategies for managing multiple Virtual Hard Drives (VHD's).
Co-founder, President
Led monthly meetings on diverse topics related to Microsoft operating systems and associated freeware available via the Internet.
Education
New Horizons - Overland Park, KS
- 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# must be embedded for the objects such as buttons, checkboxes, and combo boxes to work in Excel when published to SharePoint using Excel Services.
North Lake College - Irving, TX
- 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.
Illinois State University - Normal, IL
- Master of Business Administration
- Supplemented graduate studies with additional undergraduate accounting classes.
- Bachelor of Science, Finance major with Business Administration minor
Teaching Experience
I have always had excellent evaluations from students and peer/administrative reviews.Heartland Community College - Normal, IL
Department of Social and Business Sciences
- Financial Accounting
- Managerial Accounting
- Introduction to Spreadsheets
- Introduction to Microcomputers
Illinois State University - Normal, IL
Adjunct Faculty
Department of Finance, Insurance and Law
- Business Finance
- Personal Finance
Department of Accounting
- Financial Accounting
Graduate Assistant
Department of Finance, Insurance and Law
- Business Finance
Recognized several semesters as ISU faculty member having most positive influence on students.
