Skip to main content

What I Have Been Working On

· 5 min read

Intention

Originally this started as a page on the website that did not change. The idea behind listing out what I was currently working on was that I would have a snapshot of the technology that I was currently using as well as what I was learning. But as I revisited this page, I realized it was going to change over time and really should be a blog post. A good example would be how when I started building Tabular Data Models I spent all my time in Visual Studio. Now I rarely open Visual Studio for developing the data models but still use it for SQL Server Integration Services (SSIS).

Many Hats

I work in a small IT shop. This means that I wear many hats. I gather requirements from the business, ETL, develop the data model, write the DAX code, and help with how the data will be presented on the canvas (usually Excel or Power BI). This also means understanding source systems like Netsuite ERP and how the data on the backend is structured. I also have to be self-motivated to learn new skills and apply them to projects I am working on.

SQL Server DBA

My main role early on was that of a SQL Server DBA. We have internal applications using SQL Server for the databases. We do not have separate SQL developers so the responsibility of maintaining and fixing all of the SQL stored procedures was part of my responsibilities. I still perform that role in some capacity, but it is not my primary responsibility due to the growth of the Business Intelligence projects.

SQL Server

I use SQL Server Integration Services to pull the data from the source systems (Custom payroll application, Netsuite ERP, JD Edwards ERP) and store it in SQL Server. I create the databases, tables, and views that are needed.

Third-Party Data Model Tools

I use DAX Studio for testing measures, performance tuning, and querying the data models. For building the data models I use a combination of tools including Tabular Editor, Model Auto Build, and Power BI Desktop. Some of the large models are then deployed to SQL Server Analysis Services. One of the tools I need to learn more about is Bravo.

Building the Tabular Data Models

To build the tabular data models, I am using Model Auto Build which uses an Excel file with the metadata in it. The author describes it as laying out a blueprint of the model before creating it. You can share the Excel file with the business as it gives them insight into what is being created. It is dependent on Tabular Editor to generate the necessary assets. Since it is essentially a C# script, I can make changes to the code if needed and also raise an issue with the author if any bugs are found. Because there is no GUI it requires me to visualize the model and draw out how it should look (yes, I use giant sketch pads). It also emphasizes only pulling in the data that is needed to meet the requirements. Then I use Powershell scripts to deploy and refresh the model. With different branches on Azure DevOps, I can deploy to dev, test, and production.

My first models were built using Visual Studio. Some of which are still in Production.

We also build data models directly in Power BI.

Visualizing the Data

We have Excel and Power BI as the clients connecting to the data model. There have been projects where the delivery method is Excel where using Cube Formulas was necessary to get the desired layout of the report. The goal is always to get as much into Power BI reports and dashboards so that the data can be looked at when needed and not require opening a file located on a file share. The hope is that it will empower others to start taking action on the insights gained and not spend so much time trying to wrangle the data themselves.

Excel and Beyond

I am a big fan of Power Query and love teaching people how to use it. I have done several lunch and learn events at work to teach Power Query. I believe that anyone that uses Excel on a daily can benefit from using Power Query.

We currently use Microsoft Team Foundation Server for source control but I am transitioning some of my projects to git using Azure DevOps.

On my list of what I am currently learning or need to get up to speed on is C# (mainly for automating the data model creation in Tabular Editor), React (website), Dataflows, and Power BI Premium.

I am using Windows Terminal and Visual Studio Code (in fact that is what I am writing my blog post with).

My Passion

My first love is SQL which I still interact with daily, but I love working with the Tabular data model. My journey into business intelligence started with conversations with coworkers that were in Financial Planning and Analysis group. It started with trying to improve their quality of work life by finding solutions that could eliminate a lot of repetitive copying and pasting. It was through the relationship with the FP&A group and their participation in the Nashville Excel user group that I was exposed to Power Pivot and Power Query. Once I started building data models I was hooked. It is an ongoing journey trying to keep up with the latest features in Power BI and other tools. It is a journey that I am glad to be on.