Greg Beaumont's Data & Analytics Blog

Data, Analytics, AI, Business Intelligence posts and videos

recent posts

about

Greg Beaumont is a technology professional from Minnesota specializing in Microsoft tools.

  • NYC Restaurant Inspections Results Open Data

    This article was originally published to a blog which has been decommissioned.

    New York City also has Restaurant Inspection Results data available for use as open data, and in this article we have mashed it up with the 311 Service Requests data model that we had previously created.

    Accessing the Data

    New York City’s Restaurant Inspections Results open data can be accessed from the following link: https://nycopendata.socrata.com/Health/Restaurant-Inspection-Results/4vkw-7nck?

    The page contains details about NYC Restaurant Inspections Results, along with a link to a zip file containing the open data.  The zip file contains .txt files having data in a comma delimited format.  For this demonstration, the zip file was downloaded and the files within it were extracted and transformed using Power Pivot for Excel 2013.  

    Modeling the Data

    The NYC Restaurant Inspections Results zip file contains 4 text files. The files in the zip file are:

    • WebExtract – This file functions primarily as a fact table, but also contains attributes and detail information.  A few examples include Zip Code, Inspection Date, Score, and Current Grade.
    • Action – Codes for actions along with descriptions are contained in this file.  By filtering for rows with an [ENDDATE] greater than 2010, the [ACTIONCODE] column will become a unique key for the table.  The WebExtract table contains a column [ACTION] which can be used as a foreign key to reference the Action table.
    • Cuisine – Details for cuisine types are in this file along with a column [CUISINECODE] which is a primary key for the table.  The WebExtract table contains a column [CUISINECODE] which can be used as a foreign key to reference the Cuisine table.
    • Violation – Violation specifics are on this table, and by filtering [ENDDATE] for dates greater than 2010 the column [VIOLATIONCODE] can be used as a unique primary key.  WebExtract has a column named [VIOLCODE] that can be used as a foreign key to reference the Violation Table.

    Both the NYC 311 Service Requests and NYC Restaurant Inspection Results open data sets contain zip code data.  For the mashed-up data model, a conformed dimension for Zip Codes has been added to enable interoperability between the data sets for comparative analysis by zip codes.  An open data list of US states and zip codes is available at Data.gov, and was used as a source for the conformed Zip Codes Dimension.  The file was downloaded as a .csv file, filtered for unique Zip Codes, and uploaded into Power Pivot.  The column [ZIP Code] is used as a primary key for the column [Incident Zip] in NYC 311 Service Requests and [ZIPCODE] in the WebExtract table for NYC Restaurant Inspection Results.

    In the previous Open Data Bits article featuring NYC 311 Service Requests, separate dimensions were created for Date and Time.  The WebExtract table contains a column for [INSPDATE] that can be used to create derived columns functioning as foreign keys for the Date Dimension and Time Dimension.

    As a result of the above data modeling efforts, the high level logical model of the mash-up using NYC 311 Service Requests and NYC Restaurant Inspections Results open data looks as follows:

    Hierarchies

    While hierarchies may exist in the NYC Restaurant Inspection Results data, none were added for this demonstration.  A hierarchy for [State] > [Zip Code] was added to the Zip Codes Dimension, but is not yet necessary since both data sets apply only to New York State.

    Metrics

    The following metrics were added for the NYC Restaurant Inspections Results open data set:

    • Inspection Count – A count of rows for the WebExtract table.  This is a count of unique entries in the data.
    • Graded Count – A count of rows from the WebExtract table that have a value other than null for the [CURRENTGRADE] column.
    • C Grades – Created for use in a visualization tool, this metric counts the number of rows for which the [CURRENTGRADE] is “C.”
    • Score Avg – The average [SCORE] for rows that are not null.
    • Score Max – The maximum [SCORE] for rows that are not null.
    • Score Min – The minimum [SCORE] for rows that are not null.

    Visualizing the Data

    Once the NYC Restaurant Inspection Results data has been modeled and the metrics determined, it can be pulled into a reporting tool and mashed-up with the NYC 311 Service Requests Data.  Open Data Bits has used Power Pivot for Excel 2013 to perform this task, and the data model looks as follows in the Power Pivot Diagram View:

    The data model can then be explored using Power View for Excel 2013.  Starting with a Power View report filtered for both “DOHMC” from the NYC 311 Service Request attribute [Agency] and “Food Establishment” from [Complaint Type], data from both data sets can be visualized using interactive slicers.  The slicers are on the left hand side.  The bubble chart plots zip codes for a count of NYC 311 Service Requests on the y axis, a count of NYC Restaurant Inspection Results ([Graded Count]) that were graded on the x axis, and a count of “C” grades from NYC Restaurant Inspections determining the bubble size.  The map displays zip codes by [Graded Count] and the value of [CURRENTGRADE].  The bar chart at the bottom shows the same data as the map, but in descending order of [Graded Count]:

    After filtering for February of 2013, there appears to be a high number of 311 Service Requests for zip code “10461” relative to [Graded Count] from the NYC Restaurant Inspection Results.  Clicking on this bubble and then drilling into the “10461” zip code bubble on the map displays the addresses that were graded in that zip code:

    Finally, after drilling into the bottom bar chart, an individual address can be clicked on the map.  Rows from the NYC 311 Service Requests Violations table can then be seen for February 2013, in that zip code, for that particular address:

    Another open data set will be featured shortly, so in the meantime please provide any feedback or suggestions in the comments for this page.

  • New York City 311 Service Requests Open Data

    This article was originally published to a decommissioned blog.

    New York City has made open data available to “serve as a rich resource for developers, civic groups, and anyone else to build applications on their own.”  One of those data sets contains data for 311 Service Requests from 2010 to Present.  This data is very interesting to explore having attributes such as zip code, borough, latitude and longitude in addition to details about the requests.

    Accessing the Data

    New York City’s 311 Service Requests from 2010 to Present can be accessed from the following link:

    https://nycopendata.socrata.com/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9

    The page contains several interactive features for discovering, discussing, and accessing the data.  For the purpose of this article, data was pulled into Microsoft Excel 2013 using an OData connection.  The connection was set up using the Excel 2013 add-in Power Query, and then added to the data model in Power Pivot.  Instructions for using the OData url can be found at this link.  There are numerous methods to access the data for NYC’s 311 Service Requests, and there are also several tools on the market that can be used to work with the data.

    Modeling the Data

    Since this is the first data set featured at Open Data Bits, this article will not contain examples of mash-ups with other open data.  Instead the focus will be on setting a foundation for conformation with other data sets in the future.

    Date and Time Dimensions

    First, a Date Dimension was created to enable both hierarchical rollups of data and interoperability with other data sets that have data that is at the day level or lower.  A good overview of the need and general design of a Date Dimension can be found at this link.

    There are a few columns that include date values in the NYC 311 data including [Created Date], [Due Date], and [Closed Date].  Any of these columns could be the basis for a foreign key to the Date Dimension.  We chose to feature [Created Date] for our examples.  [Created Date] has both Date and Time components, so a derived column was created to drop the Time component from [Created Date].  As a result, the [Created Date] column can be derived to a column that is mapped to the Date Dimension at a granularity of the day level.

    For the Time component of [Created Date], a similar approach was taken.  This will allow both interoperability with future data sets and the ability for reports to interactively roll data up by second, minute, hour of the day and AM/PM.

    Details about the Time Dimension used by Open Data Bits can be downloaded from this link.

    The reason for splitting out the Date and Time dimensions separately is to reduce unnecessary row volumes in the conformed solution.   There are 86,400 seconds in a day, which is the number of rows in the Time Dimension.  If the Time and Date dimensions remained combined, there would be (86,400 x 365 = 31,536,000) rows per every year of data in the solution.

    As a result, a high level Logical Model of the NYC 311 solution looks as follows:

    Hierarchies

    Hierarchies are logical relationships within data in which parent-child relationships exist.  An example would be that Date is a child of Month, which in turn is a child of Quarter.  More details about hierarchies are available at this link.

    There does not appear to be any natural hierarchies that need to be called out when using the NYC 311 Service Requests data as a standalone source.  There are some examples of natural geographic hierarchies, but since all of the data is for New York State there is not a need to build a hierarchy for these in the solution.

    For reporting purposes, a hierarchy was created for [Agency] > [Agency Name].  It appears that some values for [Agency Name] roll up to [Agency]:

    Metrics

    The primary metric for this data set appears to be a count of service requests, which translates to a count of rows in the solution.  Metrics from the Date Dimension such as [Day Count], [Week Count], and [Month Count] can also be used when exploring the data.

    Visualizing the Data

    Once the NYC 311 Service Request open data has been modeled with hierarchies and metrics, business intelligence tools can be used to visualize the data.  For the example below, the solution was built in Power Pivot and is displayed using Excel 2013.  Here’s a screenshot of the solution in Power Pivot:

    Starting with only a filter for the year 2013, a count of NYC Service Requests can be seen by Agency, by month, by hour of the day (24 hour clock), and by complaint type:

    The hierarchy for the Agency member “DSNY” can be expanded to view the Agency Names within that Agency in descending order of service requests:

    Filtering the report for Incident Zip of 10005 (near Wall Street) using the slicer on the bottom left, the charts show data for that location only.  It appears that there was a spike in 311 Service Requests to the Agency “DEP” in October of 2013:

    Filtering for the Agency DEP in the left side slicer, and then double-clicking on the month October 2013 in the chart shows that the data contains a large count of service requests on October 9th, 2013.  The 24 hour clock shows that a high volume of service requests in that month were in the 9pm-12am range.  313 of the 393 service requests were for “noise”:

    Finally, double-clicking on the NYC 311 Count value yields a list of individual service requests that can be used to determine the details behind the data for zip code 10005, in 2013, for the Agency DEP, on October 9 2013:

    My next article will feature New York City restaurant inspection results mashed-up with 311 Service Requests data.  

  • Video: Forecasting using Regression Analysis in Microsoft OLAP Cubes

    This article was originally published to a blog which has been decommissioned.

    The following video walks through the process of creating a linear regression line using MDX in a Microsoft OLAP cube:

  • Forecasting with an MDX Linear Regression in an SSAS OLAP Cube

    This article was originally published to a blog which has been decommissioned.

    A few days ago I published an article detailing a method to create regression line calculations in an Analysis Services OLAP cube using MDX. It is the starting point for this article. My instructional video detailing the entire regression line and forecasting process can also be found at this link.

    Regression lines are a great way to visualize trends over time. My previous article details how to view a regression line over time periods that have already happened. Regression lines are also frequently used to forecast values for future time periods. Basically, extending a regression line into the future is one method of forecasting. Below I will walk through one method to extend a linear regression line to future values using MDX in an SSAS OLAP cube.

    Continue reading this post at the following link: https://web.archive.org/web/20140219072558/http://blog.gnetgroup.com/bi/2013/03/28/forecasting-mdx-linear-regression-ssas-olap-cube/

  • Create a Regression Line in an SSAS OLAP Cube with the LinRegPoint MDX Function

    This article was originally published to a blog which has been decommissioned.

    During a discussion about OLAP cubes for Business Intelligence data visualization and analysis, I was asked if regression lines could be added to cubes as calculations within Analysis Services. I had previously browsed the MSDN pages for the MDX functions LinRegPoint and LinRegSlope, but since I had never used them I replied “You should be able to create a regression line as an SSAS calculation, but I’ve never actually tried to do so myself.” I don’t like qualifying a statement with “should be able to,” so I did some research which has now evolved into this article.

    Continue reading this post at the following link: https://web.archive.org/web/20140219051005/http://blog.gnetgroup.com/bi/2013/03/26/create-regression-line-olap-cube-mdx-linregpoint/

  • Excel with PowerPivot for Custom Dimensions in Reports Blended with EDW Data

    This article was originally published to a blog which has been decommissioned.

    Comprehensive Enterprise Data Warehouse (EDW) architecture and management requires a disciplined, centrally-managed strategy. Often, analysts within an organization require dimension designs that are complex and evolving. For example, subject matter experts for healthcare organizations often deal with dimensional attributes for healthcare performance measures such as ORYX, HEDIS, and Meaningful Use.  Such cohort inclusion lists can be robust and are frequently evolving.

    Continue reading original article at this link.

  • Video: The GNet Group SightN2 for Healthcare BI Solution

    This article was originally published to a blog which has been decommissioned. Several years ago I contributed to the project reviewed in the video which reviews a solution built using HCUP Healthcare data:

    Read the original post at this link!

  • Create a BI Histogram with Dynamic Bins using DAX in PowerPivot

    This article was originally published to a blog which has been decommissioned.

    When building a histogram, how many bins should it have?  There’s no definitive answer to the question, but the number of bins used for a histogram can drastically impact how visualized data is interpreted.  Details such as data distribution patterns and sample quantity can impact how data is displayed in bins, and quite often the number of bins is arbitrarily chosen.

    In a Business Intelligence solution, determining the number of bins is more difficult than with static reports since the volume and distribution of data can change with updates.  This article intends to share one method for building bins that will automatically compensate for updates in total data quantity and range.

    Numerous methods exist for determining the number of bins in a histogram.  For this example, I’ve chosen Sturges’ formula.  I wish that I had a mathematical basis for choosing it, but unfortunately I do not.  I chose Sturges’ formula because it works and it can easily be coded into a BI solution.  The equation is: k = [log2N + 1] where k represents the number of bins.  The equation works well with almost any quantity of data in a typical BI solution.  A count of 100 will have 7 bins and a count of 1 billion will have 30 bins.  Either of those bin quantities is visually consumable on a histogram.

    Continue reading original article by clicking here!

  • Video: Power View BI Motion Charts in SQL 2012 with FAA Aircraft Wildlife Strike Data

    This article was originally published to a blog which has been decommissioned.

    The video posted at the end of this article is the capstone to the FAA Wildlife Strike Data series.  To review the entire series and experiment with building your own copies of these reports, links to all articles and videos in the series are as follows:

    1. BI Design for FAA Aircraft Wildlife Strike Data (click here to read)
    2. FAA Aircraft Wildlife Strike Data Model and Dashboard in PowerPivot (click here to read)
    3. Video: FAA Wildlife Strike Data BI Dashboards with PowerPivot (click here to read)
    4. Video: Power View BI Motion Charts in SQL 2012 with FAA Aircraft Wildlife Strike Data (this article)

    The video below demonstrates the use of the FAA Wildlife Strike Data for the creation of a motion scatter chart in Power View.  Power View is a useful new BI tool available with SQL Server 2012.  Motion charts, like those presented by Hans Rosling over the last few years at TED talks, are a powerful new tool for presenting data in a manner that can captivate an audience:

    Original article continues here.

  • Video: FAA Wildlife Strike Data BI Dashboards with PowerPivot

    On January 15th, 2009 the news media was focused upon a passenger plane that had safely completed a water-landing after both engines had been disabled by a flock of Canadian Geese. Known as the “Miracle On The Hudson,” all 155 occupants survived. While airborne birds have long been a nemesis of aircraft, the publicity of this incident most likely piqued interest in the topic for many people. Fortunately, the Federal Aviation Administration (FAA) provides data in a format that can be integrated into a Microsoft BI dashboard using PowerPivot.