What Three Issues Cause the Most Pain in Microsoft Access.

In the dozens of Microsoft Access issues I have fixed since I started working with Access in 1997, I would estimate that 95% of the issues were one of the below: Issue #1: Database is growing in size at an alarmingly rapid pace and seems way too large for the amount of data it is storing. Solution: Enable the Compact and Repair option. I cannot think of a single reason why this would not be enabled and I am not sure why this is not an automatic feature. Sure, it makes closing the Access database file slower, because the database file relinquishes unneeded hard drive storage when it closes, but this is a trade-off that is well worth it. Issue #2: Database cannot be troubleshooted because tables and queries are hidden. When you open the database, only a "switchboard" form is displayed or a macro is automatically runs and the rest of the database objects are hidden. Solution: Open the database file while holding down the Shift key. Although hiding tables and queries from users who could do harm to the database is good design, if you are fixing a database you need access to EVERYTHING. Issue #3: Database is corrupt beyond repair. You are getting cryptic error messages when you open the file and you cannot make the messages go away. Solution: Sometimes it is much more expedient to rebuild an Access database from scratch rather than repairing a corrupted file. Building from scratch is not that difficult, you just need to import all of the Tables, Queries, Forms and other database objects from the original database file to the blank database file.

Mapping Crime in Denver

In previous posts, I showed how to transform public (and free!) crime data from the City of Denver's Open Data Catalog into an in-memory analytical playground using Microsoft Power View on SharePoint and a SSAS Tabular database, and 
how to embed a Power View in Excel 2013.

In this post, I am going to demonstrate some of the mapping features in Power View.  I will demonstrate in Excel for the sake of convenience but the mapping features in Power View for SharePoint are similar. 

SlideShare demonstration:

Video on How to Use Power View Mapping Features:

Video on Interacting with the Power View Map:

Thank you for reading this post.  I hope it will give you some ideas of the capabilities of this fantastic data visualization tool.

Please feel free to call me or email if you have any questions about this post or if you would like to discuss any projects you are working on.

Noah@SQLFixItGuy.Com

347-678-8279

Posted By: Noah Meyer, aka, the SQL Fix-It Guy

Noah Meyer is a Denver-based Business Intelligence developer, architect and tutor who has spent the past two decades helping organizations, big and small, turn their data into actionable insights.  He has built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and is am available for work on projects. 

Where is crime growing fastest in Denver?

In my last post I showed how to transform public (and free!) crime data from the City of Denver's Open Data Catalog into an in-memory analytical playground using Microsoft Power View on SharePoint and a SSAS tabular database.

In this post, I am going to show how to build the same Power View data visualizations, but only using Excel 2013 this time.  In addition to looking at which areas have the highest number of crimes, we will also investigate the rate of growth (or decline) in neighborhoods throughout Denver. 

First, we can see that the following types of crimes declined from 2009 to 2013:

1. theft from motor vehicles
2. white-collar crime
3. auto-theft

Next, we see an increase in the following categories:

1. "all-other-crimes"
2. drug-alcohol
3. larceny
4. public disorder

Overall, the number of crimes increased from 2009 to 2013.  2014 data is only up to mid-November so it shows a decrease from 2013 but it will probably be higher then 2013 when the data is updated in the new year.

Shown another way, the overall trend definitely shows an increase in arrests:

If we look at number of arrests per year by area, we can see the following neighborhoods have the fastest rate of growth:

1. Five-Points
2. CBD (central business district)
3. Capitol Hill
4. Montebello
5. Stapleton
6. East Colfax

It is hard to discern because the above chart is so cluttered. If you filter for just those above mentioned neighborhoods, it becomes easier to see:

Below is a video of using Power View within Excel 2013 to make these slides. 

Building this Power View in Excel is much simpler and requires less steps than when you use Power View in SharePoint. In a later post, I will talk about the differences between Power View in Excel and SharePoint. 

Step 1: Get the Data

File can be downloaded for free at the City of Denver Open Data Catalog 

Step 2: Create Power View in Excel 2013

1. Launch Excel 2013.

2. Create New Workbook.

3. Insert Power View:

4. While the Power View sheet is activated, click on PowerPivot menu and then click on the Manage icon:

5. When the new PowerPivot window appears, click on the icon that say "From Other Sources":

6. In the Table Import Wizard, select the Text File source, usually at the bottom of the wizard:

7. Click on Next, then browse to where you save the Crime CSV file on your computer.

8. Make sure that "Use first row as column headers" is checked:

9. Go into Data View in order to add a calculated column to the flat file that you just imported:

10. Create a new column in the data table and put a DAX formula such as =Year([First_Occurrence_Date]). Rename the column to "YEAR" after entering the DAX formula.

11. Close the PowerPivot window which will return you to the Power View window.

SQL Fix-It Guy
I am a Denver-based database developer, trainer and coach who has spent the past two decades helping leading organizations turn their data into competitive intelligence.  I have built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and I am available for projects small and large.  

Noah Meyer (noah@sqlfixitguy.com)
Cell: 347-678-8279

Visualizing Crime in Denver

Thanks to the City of Denver's Open Data Catalog, anyone who is interested can download crime statistics for the past few years. The level of detail is amazing! 

I decided to use this data to showcase one of my favorite Data Visualization tools: Microsoft Power View.  Although Power View is available in Excel 2013, I am using Power View for SharePoint which is slightly different than the Excel version.

Video of Using Power View to Visualize Denver Crime

The data I used is since the beginning of 2009 through the end of October 2014. There has been much improvement in crime in some neighborhoods over that time period.  In a future post, I will be showing the growth or decline rate of crimes in specific neighborhoods.

Below is how I was able create the Power View above.

Step 1:  Get access to a SharePoint 2010 or 2013 environment with SQL Server 2012

If you don't have access to the above environment, you can still try out this exercise by using a Microsoft TechNet Virtual Lab here.  Please be aware that anything developed on a TechNet Virtual Lab is temporary and will be permanently deleted once you end your lab session. 

Step 2: Get the Data

Get free data file at http://data.denvergov.org

Click on download button and save CSV file to your computer

Step 3:  Prepare the Data for SQL Server Import

The SQL Server Import Export Wizard easily imports data files of different formats into SQL Server.  I tried importing directly from the CSV file, but the wizard consistently gave errors after importing half the rows.  I found that importing from an Excel almost never fails, so I quickly convert the CSV into an XLSX first, and then import into SQL Server.

 1. Open the CSV file in Excel.

2. Save file as an XLSX.

Step 4: Import Data into SQL Server

1. Launch SSMS (SQL Server Management Studio).

2. Create new database, call it "DenverCrime".

3. Right-click on DenverCrime database, select Tasks then Import Data.

4. In Import and Export Wizard, select Excel as the Source.  

5. Select SQL Server as Destination. The new table will automatically go into the DenverCrime database.

6. Copy data to a new table in the DenverCrime database.

Step 5: Create a SSAS Tabular Database

1. In Visual Studio 2012, create a new Analysis Services Tabular Project.

2. Import Data from SQL Server by selecting Model, Import from Data Source

3. In Table Import Wizard, select Microsoft SQL Server.

4. Specify server and database where we created the DenverCrime database.

5.  When prompted for "Impersonation" choose Service Account option.

6. Click close when prompted that Import Wizard is done.

Step 6: Deploy Tabular Project to SSAS Tabular Server

Specify the SSAS server and database where the tabular database is going to be deployed to.  Later, we will create a Power View in SharePoint that sources its data from this location.  

1. In Solution Explorer, right-click on the top file, select properties.  For Server, specify "sqlbi\TABULAR".  For database: "DenverCrime".

2. Right-click on the top file again and this time select Deploy.

Step 7: Create Data Connection in SharePoint

We will create a connection file that is called "BI Semantic Model Connection" or just "BISM" for short.  This connection file will tell SharePoint how to connect to the DenverCrime SSAS Tabular database that we created in the previous step.

1. Go to a BI Site Collection page on your SharePoint.  In the virtual lab example, this would be http://sqlbi/SitePages/Home.aspx.   Then, click on Shared Documents.

2. Create a BI Semantic Model Connection document by clicking on Documents, New Document. 

3. Put information about the SSAS Tabular database that we just deployed from Visual Studio.  Where it says Workbook URL or Server Name, specify "sqlbi\TABULAR".  Where is says Database, write "DenverCrime".

4. Click on OK.

Step 8: Create a PowerView in SharePoint

1. Click on the down arrow to the right of the BISM connection file.

2. You will now be in the new Power View report.  Watch the video at the start of this blog to see how the data within this report can be visualized with charts and graphs.

SQL Fix-It Guy
I am a Denver-based database developer, trainer and coach who has spent the past two decades helping leading organizations turn their data into competitive intelligence.  I have built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and I am available for projects small and large.  

Noah Meyer (noah@sqlfixitguy.com)
Cell: 347-678-8279

Course on Microsoft Self-Service Business Intelligence using SharePoint 2010/2013, SQL Server 2012, SSAS Tabular and PowerView.

$149 - Special introductory price for Denver area companies until end of year only.

Kick start your Self-service BI initiative with training from a Microsoft BI leader local to Denver.  Whether you have already explored self-service or not, if you have SharePoint 2010 or 2013 and SQL Server 2012, I can show you enough of the basics to get you started on your own, using the tools you already have!

The following will be covered at your offices from either 8:30AM to 12 Noon or 1:00PM to 4:30PM:

1. Differences between SSAS Tabular and SSAS Multidimensional and why you should consider tabular.

2. How to add data sources into a tabular model: from SQL Server, odbc, flat-files, Excel spreadsheets, SSRS reports.

3. Modeling in tabular by joining the disparate tables.

4. Creating calculated measures, rankings and calculated columns using DAX.

5. How to deal with tabular annoyances such as:

   Memory Constraints preventing the model from refreshing.

   Joining tables that have a "many-to-many" relationship.

   Queries timing-out.
   
   Data "sticking" to the SSAS database even though it is no longer in the source systems.

6. How to create a SSAS Tabular data connection in SharePoint 2010 or 2013.

7. How to create lightning-quick, stunningly beautiful data visualizations using PowerView in SharePoint 2010 or 2013.

Contact me now for more information on this special introductory course offer.

Noah Meyer, aka The SQL Fix-It Guy, is a Denver-based database developer, trainer and coach and has spent the past two decades helping leading organizations (banks, hospitals, energy companies and government agencies) improve their reporting capabilities by fixing and fine-tuning their customized software implementations.

The short-cut key in MS Access that can save you heart-ache and thousands of dollars

The Shift Key.

It should be on the first page of every Access how-to guide, but it is a well known secret among Access professionals.

Holding down the shift key while opening an Access file prevents forms from automatically popping up and macros from running.  Suppressing forms and macros is sometimes all that is needed to repair a database.

I had a client who was stuck on an old PC for several years because they could not convert there database from MS Access 2005 because it had a link to a DLL file that was no longer available in later versions of Access.  It turns out the missing file was not even needed, but every time the database was opened a VBA macro would launch and immediately throw an error message, and the client was stuck.  Stuck for SEVERAL YEARS.

After instructing the client to open the database while holding down the shift key, it took them all of 5 minutes to remove the reference to the missing DLL file, and the database worked smoothly thereafter on MS Access 2010 and 2013.

IT Success Stories:  True accounts of clients who overcame vexing technical issues.

About the Author:
Noah Meyer, aka The SQL Fix-It Guy, is a Denver-based database developer, trainer and coach and has spent the past two decades helping leading organizations (banks, hospitals, energy companies and government agencies) improve their reporting capabilities by fixing and fine-tuning their customized software implementations.

If You Could Teach a Course on Database Design, What Would You Cover?

I am teaching an introduction to database design course next year and I have sketched out the course below.  I would love to hear from other database professionals to find out what you feel should be included in a first course.

Outline of Course

1. Currently popular RDBMS implementations (oracle, sql server, MySQL) and how each is typically used.

2. Tables and datatypes.

3. What does "relational" mean?

4. Querying - selects, updates, deletes.

5. Normalization and why it's useful.

6. Transactions and isolation levels.

7. Programming - stored procedures, triggers, functions, cursors.

8. Database engine optimization, query plans, indexes.

9. Oltp vs olap - how highly transactional systems dictate different database design then systems that are primarily for reporting.

10.  Advanced topics - big data, BI, no sql, data visualization.

About the Author:
Noah Meyer, aka The SQL Fix-It Guy, is a Denver-based database developer, trainer and coach and has spent the past two decades helping leading organizations (banks, hospitals, energy companies and government agencies) improve their reporting capabilities by fixing and fine-tuning their customized software implementations.