Martin Ignacio Alonso

A live-connection Power BI report and a series of scripts that enable access to Active Directory service accounts in the most non-privileged way to the Microsoft database job tables. I also created several database views that joined everything together to feed a nice mobile view in Power BI. This project allows us to monitor all our loads in real time, organized by priority, without logging into the system.

The main use case that motivated me to create this was extracting information from a Microsoft SSAS multidimensional cube from a server with very short timeout windows set up. As I needed to extract lots of data, I realized that instead of extracting one big chunk of data, I had to extract multiple smaller batches. The creation of these batches became very tedious and error-prone, so I created this library to assist me in creating the MDX query for each of the batches automatically. The library also helps in creating single-batch MDX.

Created a compensation calculation system with the following considerations:
• SOX compliance
• Separation of roles
• Microsoft SQL Server back end
• Power BI paginated reports for compensation owners to verify the numbers
• C# interface to input parameters and retrigger compensation calculation jobs

One of the many ETLs I created uploads information to Einstein Analytics. I enabled this by leveraging the existing Informatica installation. I created this small SSIS component to trigger the Informatica linear task flow for loading the data from our database into Einstein Analytics.

One of the main issues with out-of-the-box dataflow generators used by Einstein Analytics to start up a new instance is that they create big spaghetti dataflows, making it extremely difficult to maintain. In addition, because of the nature of our configuration, security predicates needed to be configured on all datasets.

I created different tiers of Einstein Analytics Dataflow:

Tier 1: Generated master data objects, such as opportunity, account, and partner. The main rule is that there are no augments based on EdgeMarts.

Tier 2: You only feed from EdgeMarts; that way, you don't have to reinvent the wheel every time you need to augment information from a standard object.

One of the many types of data sources we handle is flat files (CSV). Our flows usually extract the data from the CSV and insert it into a table that contains the correct data types. Power users access this table in a centralized way to create multiple reports. Some users upload these files into a shared windows folder.

Originally, these files were picked up by a SQL Agent job that would constantly check if a folder contained the file. The problem with this approach was that we would end up with a job log stating that a step took two days to run, which was technically true, but the actual processing time was minutes.

This discrepancy led me to create a C# Windows service that, based on a JSON configuration file, would listen to a set of folders, and whenever a new file was detected, a SQL Agent job would be triggered. With this solution, the actual execution times were accurate.

A C# Windows service that traces SSAS Tabular 2012 transactions based on the 2012 version of ASTrace (https://github.com/microsoft/Analysis-Services/tree/master/AsTrace). I developed this version of the service that allowed us to do the inserts in the database by using a more secure stored procedure. I don't care for the actual query being executed, so I don't store it, but this can be changed easily.

Many of the tabular data models we create feed from multidimensional cubes developed by other areas. These cubes refresh daily at a recurring but not very precise time, which varies greatly depending on the day. Because of this restriction, a scheduled refresh of our data models was out of the question, as it would often miss the refresh of the source system.

This restriction led me to craft a database solution and SSIS ETL that queries the last refresh date of each source every ten minutes. Whenever an update on a specific data source is detected, a meta job containing all the SQL Agent jobs that depend on the said data source is triggered.

This system also has a series of stored procedures used to create jobs programmatically and hook up regular jobs to meta jobs of each relevant data source. The result of this project is a system that loads everything in a clever way without human intervention.

I installed five different servers to enable my area to generate SSAS Tabular models:
1. SSAS 2012 in tabular mode with Power BI Gateway (production)    
2. SSAS 2012 in tabular mode with Power BI Gateway (development)
3. SQL Server 2012 with SSIS (production)
4. SQL Server 2012 with SSIS (development)
5. TFS 2012

All the service accounts were configured with Active Directory Users that had SPNs configured to enable Kerberos delegation. The SQL Servers are used as a back end to the SSAS servers. This allows us to create more complex cubes. I also implemented a small DevOps strategy to deploy data models into production by leveraging the TFS server. Finally, the Power BI Gateway enables us to create reports against on-premise cubes.

Installation of SharePoint 2013 Server with three tiers—database, application, and front end—leveraging multiple technologies to create a portal to centralize all our business intelligence needs. This enabled my team to transform multiple Excel reports, originally refreshed manually on a daily basis, into an automated reporting system, allowing us to grow significantly.

Technologies:
• PowerPivot
• Power View
• Reporting Services
• Search services
• Kerberos delegation