Getting started with Microsoft Power BI using Google Merchandise store data.


Getting started with Microsoft Power BI using Google Merchandise store data

          While I’m as loyal to Excel as the next analyst, when it comes to data visualization and interactive dashboards, Tableau is my tool of choice. If I need to analyze large data sets, I prefer to get cozy with the data by writing SQL queries in whichever database environment such data might be stored. In the meantime, the world does not stand still, and Microsoft has been making substantial progress with a product offering they called Power BI . In fact, this tool offers data preparation, data discovery, dashboarding and custom visualization features starting with a free version for up to 1 GB of stored files and a modest $10 monthly plan for the beefed up PRO version. It’s definitely long overdue, but I finally got around to playing with both: Power BI desktop and cloud-based versions, all while using publicly available data from nonetheless, but Google’s merchandise store. , available through their demo Google Analytics account. Which other etailer can boast growing their Cyber Monday sales by 274% to $54K, while keeping their marketing advertising budget under $ 100?

Continue reading

How to pass an analytics job test – Part II – MS Excel.


How to pass an analytics job test - Part II - Microsoft Excel

          Even with the rise of use of R, Python, SAS and other more scientific analytical tools, Microsoft Excel remains the most popular data analysis tool. While we have gone over a solution for an analytics job test in SQL last month , you are much more likely to encounter a job test in Microsoft Excel for your next analytics opportunity. While I would personally argue that this particular test is actually better solved with SQL, the employer believes that the applicants instead need to apply their Excel skills to demonstrate their proficiency and acumen. As before, we should start by asking questions about the problem at hand and trying to get as much clarification as needed or state our assumptions. However, since spreadsheets are less forgiving from the presentation point of view than the databases, I would strongly recommend that we would also take a few minutes to format any workbooks provided by the prospective employer. Chances are they would recognize your level of professionalism by looking at clean and presentable file. Your stylistic preferences might be different, but as a minimum I would remove gridlines, add filters/format as tables larger datasets, freeze panes, and add at least one to two colors to the otherwise monochrome layout.

Continue reading

How to pass an analytics job test – Part I – SQL.


How to pass an analytics job test - Part I - SQL

          With the demand in data analytics professionals growing stronger than ever, recruiters find themselves in a peculiar position of having to screen hundreds of potential, seemingly qualified candidates. Some firms turned to the proven selection tool: pre-employment skills assessments; and analytics-related tests are on the rise, especially for the junior level industry positions. No tests are the same, but most are designed with the sole purpose of gauging candidate’s cognitive ability to understand the problem at hand and having the technical know-how to implement a working solution. Two types of analytics tests that my students shared involved using either: Microsoft Excel or SQL language. Most of relational databases can be queried using a dialect of SQL, and as such, knowledge of SQL is as essential for a data analytics professional, as their excellent communications skills. In this post we will go through an example of a SQL job test, while in the next article we would focus on an Excel problem.

Continue reading

Microsoft Excel personal macro workbook.


Personal Macro Excel Workbook

          We have already explored different Excel VBA macros here, here, and here, . While the power of Excel VBA is limited only by our creative imagination, the real limitation of a typical VBA code comes from the fact that it resides in the workbook where it was saved, and as such, can only be revoked while this file is open. This is where the personal macro workbook comes into play: if we save our code in this centralized place, we could use it in any Excel workbook on our local drive. This productivity hack would help us perform repetitive tasks (formatting and data presentation pet peeves, anyone) by automating them. The easiest way to save your code into PERSONAL.XLSB workbook is through recording a macro.

Continue reading