Kent Bradshaw and I had a blast delivering more free training from Enterprise Data & Analytics today – this time it was all about Enterprise SSIS Execution. If you were unable to attend, please enjoy the recording here:
One of the things Eugene Meidinger found frustrating when he first learned Power BI was all of the behind-the-scenes configuration required to bring Power BI to the enterprise. It was easy to find information about charts and graphs, but difficult to learn about how all the moving parts fit together.
This course focuses on two main areas: data wrangling and administration.
Session 1: Database Theory
When creating a model, it is important to know Power BI is optimized for star schema in particular and filtering/aggregating in general. Within Power BI lies a columnar database that has really good compression. This means Power BI’s model can handle a certain amount of flattening/denormalizing gracefully.
Business users are Power BI’s target demographic. We begin our exploration of Power BI with a review of database fundamentals for business users, covering topics such as:
Power BI offers two data manipulation languages:
Power Query (M)
(3 languages if you include R!)
Session 2: Power Query (M)
Power Query is designed for business users. It started as an Excel add-in to assist users familiar with the Excel formula bar. One result: Power Query surfaces an intuitive graphical user itnerface (GUI), but behind the scenes M syntax is generated.
In this session, Eugene discusses and demonstrates tips and tricks for using Power Query to clean and prepare data.
Session 3: DAX
To model and add context to data Power BI users apply DAX. DAX appears deceptively simple, very similar to Excel formulas – but DAX requires thinking in terms of columns and filters, not in terms of rows.
In this session, Eugene scales some of the steeper slopes of the DAX language learning curve.
Session 4: Data Gateways
Many enterprises practice hybrid data management in which some data and services reside in the cloud while other data and services reside on-premises. Data gateways are a way to bridge the Power BI service (in the cloud) with on-premises data.
in this session, Eugene discusses and demonstrates Data Gaterway installation and configuration. Topics include:
In this session, Eugene compares and contrasts Power BI licensing scenarios, including:
Power BI Pro
Power BI Reporting Server
Power BI Premium
Because it can be difficult to keep up with all of the options, Eugene discusses and demonstrates several ways to deploy Power BI dashboards:
Organizational content packs
Publish to web
Power BI Premium
Power BI Embedded
Power BI report server
Session 6: Security and Auditing
Securing Power BI and the data it surfaces is no longer optional.
In this session, Eugene discusses and demonstrates:
Data access management
Row-level security in Power BI and SSAS
Unified Audit log for Office 365
Data Gateway configuration
In conclusion, Eugene says, “Overall I’m pretty proud of the contents. This is the kind of course I wish I had been able to attend 3 years ago.”
Starting out as an accidental DBA and developer, Eugene Meidinger now focuses primarily on BI consulting. He is a Pluralsight course author who has been working with seven years’ SQL Server experience. Eugene holds SQL Server certifications and regularly presents at community events including SQL Saturdays and his local user group. His current focus is Power BI and related areas.
SQL Server Integration Services (SSIS) is a powerful enterprise data integration tool that ships free with Microsoft SQL Server. Join Andy Leonard – Microsoft Data platform MVP, author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – and Kent Bradshaw – Database Administrator, Developer, and Data Scientist at Enterprise Data & Analytics – as they demonstrate several ways to execute enterprise SSIS.
Join this webinar and learn how to execute SSIS from:
We began communicating regularly with SentryOne soon after they acquired the SSIS people and products from Pragmatic Works. Becoming a partner just made sense because we have been recommending Task Factory and LegiTest for years.
We are honored and excited to be a SentryOne Consulting Partner!
Shannon is a data engineer, data scientist, BimlHero (though not listed on the page at the time of this writing), and shepherd of the Biml Interrogator open source project. If you use Biml to generate SSIS projects that load flat files, you need Biml Interrogator.
Shannon, Kent Bradshaw, and I are also co-authoring a book tentatively titled Frameworks. (Confession: Kent and Shannon are mostly done… I am slacking…)
Shannon brings a metric ton of experience to serve our awesome clients. He has years of experience in data analytics, serving recently in the role of enterprise Lead Data Scientist. Shannon’s experience spans supply chain management, manufacturing, finance, and insurance.
In addition to his impressive data skills, Shannon is an accomplished .Net developer with enterprise senior developer experience (check out Biml Interrogator for a sample of his coding prowess).
Shannon is a regular speaker at SQL Saturday events, presenting on topics that include Business Intelligence, Biml, and data integration automation. He is a gifted engineer with experience in framework design, data integration patterns, and Azure who possesses a knack for automation. Shannon is an avid “tinkerer” who enjoys learning. He has experience implementing Azure Machine Learning and applying AI to predictive analytics using sources classified Big Data. Shannon is also a practitioner of data integration DevOps with SSIS. In other words, he fits right in with our team here at Enterprise Data & Analytics!
I am a data guy with a passion for partnering with clients to solve their database and technology issues. Over my career, I’ve played all the roles: database developer, administrator, business intelligence developer, and architect, and now consultant. I’m the guy you call in when you have the impossible problem and everyone tells you it cannot be solved. I automate solutions in order to free your current staff to do the higher value tasks. I bring solutions outside of traditional relational database solutions, in order to find the shortest path between you and your goals.
As an accomplished Microsoft SQL data professional, recognized BimlHero, and practicing Data Scientist, I’m the resource you need to extract the most value from your data.
I’m humbled and thankful and excited to watch Enterprise Data & Analytics continue to (quietly) grow – adding cool people (another announcement is forthcoming) and service offerings like Data Concierge. It’s very cool to watch!
Welcome Shannon! I’m honored to work with you, my brother and friend.
I’ve made statements about “two kinds of developers” for years. These statements are false inasmuch as all generalizations are false. The statements are not designed to be truisms. They are designed to make people think.
Last week – while presenting a full-day pre-conference session at the PASS Summit 2018 and again when delivering a session about Faster SSIS – I repeated the sentiment shown at the top of this post:
There are two kinds of developers: 1) Those who use source control; and 2) Those who will.
I follow up with: “Because if you do not use source control, you will lose code one day and it will break your heart.” Audience members laugh and the point is made.
More Than Two
There are myriad types of developers. And that’s a good thing. Why? Because there are myriad problems developers face in the wild and those problems need to be solved.
There are no one-size-fits-all solutions. If you attended the PASS Summit last week you likely saw some really cool demos. I know I did. And you may have thought – or even been told – that this, this right here is the answer for which you’ve searched your entire career.
There’s a possibility that the people selling you on whatever-this-is are absolutely correct. There’s a greater possibility that they are less than absolutely correct.
I write this not to disparage anyone’s solution (or my own solutions). Promise. I write this to dissuade the disparaging of anyone else’s solution (because that also happens).
The Bottom Line
Goldilocks. The bottom line is we all want the Goldilocks solution. We want the just-right solution that maximizes efficiency and minimizes complexity.
That’s just hard.
I can hear you thinking, “Why is maximizing efficiency and minimizing complexity hard, Andy?” I’m glad you asked. Solutions are a moving target, part art and part science, and the only way to learn where and when to draw the art-science line is experience.
A Moving Target
Maximizing efficiency and minimizing complexity is hard because it’s not at all the bottom line; it’s a line in the middle – a balancing of mutually-exclusive demands on your time, expertise, and energy.
Plus, it shifts.
Everything scales. Things scale either up and / or out or they scale down and / or in. In general (generality warning!), down and in is “bad” and up and out is “good.”
Experienced architects understand subtle nuances; the art part of the art / science of enterprise software. When you speak with an experienced architect, you may hear her say, “It depends,” often. Good architects will finish the sentence and share at least some of the things upon which “it depends.” Less-experienced architects will present naked scalars and polarized advice.
Naked scalars are numeric values in a vacuum. They are unsupported because most are unsupportable. In other words, they are lies. Now, “lies” is a pretty harsh word. I prefer an engineering definition for the word “truth” that sounds an awful lot like the oath witnesses are asked to swear in US courts:
“Do you promise to tell the truth, the whole truth, and nothing but the truth, so help you God?”
This oath covers falsehoods that are shared, yes; but it also covers omissions of the truth.
Examples of naked scalars:
“97% of engineers believe ____.”
“10% of the people I know have practiced ____ successfully.”
Polarized advice can be a special case of naked scalars, advice focused on 0% and 100%. Polarized advice may or may not include scalars (naked or otherwise).
Examples of polarized advice:
“I’ve never seen a good use case for ____.”
“You should always ____.”
Are naked scalars and polarized advice always bad and wrong? Nope. That would be a generality (and we covered generalities already, did we not?).
Managing the Risk of Inexperience
What exactly is a consultant communicating when they engage naked scalars or polarized advice? They are signalling a lack of experience. They are, in effect, stating, “I do not have experience with ____.”
How do you manage the risk of inexperience? You hire people – architects, especially – who understand there are good reasons systems are designed as they are. They will say things like, “I’m not sure why this was designed this way,” and mean it. It’s not a criticism; it’s an admission of curiosity. Trust me on this: You want curious consultants. They are more likely to identify a solution that solves the problem you are trying to solve in a way that doesn’t create new problems. Returning to the good reasons systems are designed as they are…
Are (or were) the good reasons, well, good? Sometimes.
Do the good reasons scale? Sometimes.
Do the good reasons stand the test of time? Sometimes.
Good architects discern the baby from the bath water. Their experience separates good architects from the crowd. Not-as-good architects are less flexible, less willing to learn, and loathe to admit mistakes.
Let’s face facts, though: All architects and developers know what they know and don’t know what they don’t know. Better architects recognize these uncomfortable truths and mitigate them.
One way to mitigate inexperience – the best way, in my opinion, is to work with others.
The Story Of Us
At Enterprise Data & Analytics, our consultants and architects work together as a team. Our diverse group is a strength, bringing perspective to bear on the problems you are trying to solve. Our experience levels vary, the software and tools with which we work vary, and our demographics vary. As owner, I am honored to lead a team from diverse cultural – as well as diverse technical – backgrounds.
I didn’t set out to build a cultural- / age- / gender-diverse team. I set out to find the best people – to do what Jim Collins describes as “getting the right people on the bus.”
I found, though, that focusing on getting the right people on the bus had the side-effect of building a cultural- / age- / gender-diverse team.
As an added bonus, people of different genders approach problem-solving differently. People of different ethnicity pick up on stuff – especially cultural stuff, including enterprise culture – that people of other cultures miss.
EDNA‘s diversity is a strength that emerged unintentionally, but emerged nonetheless. As Chief Data Engineer, it’s very cool to watch our less-experienced consultants growing into more-experienced consultants and architects, while at the same time watching our people interact and perform as a team – each member catching stuff and contributing ideas because of their unique perspectives.
I can hear some of you thinking, “We’re on a budget here. Don’t good architects cost more than less-than-good architects, Andy?” I feel you. The answer is, “No. Good architects cost less than less-than-good architects.”
It’s often accurate that good architects cost more per hour than less-than-good architects. Do you know why good architects charge more per hour?
Because they are worth more per hour.
But consider this: “Time will tell” is a tried and true statement. Like good wine, the likelihood a generality is accurate improves with age. If enterprises continue to hire an organization – like Enterprise Data & Analytics or any other firm – to help them solve the problems they are trying to solve, then the folks shouting them down may be doing so in an effort to compete. Competition is fine, but I never hire anyone who talks bad about other clients or the competition. Why? They’ve demonstrated the capacity to talk bad about me at some later date.
I love our team! I love our expertise! I love our diversity! I love that we always deliver value!
It all started when GoDaddy created a DMZ for SQL Server databases. I found this functionality in 2008 and asked myself, “Self, how might we use this?”
Since That Time…
There have been two major iterations of AndyWeather. I use weather data collected during the first iteration for training purposes at SSIS Academy and when delivering training to Enterprise Data & Analytics customers.
The setup of the second iteration is fairly straightforward:
The Acurite Weather Station consists of an instrument pack plus a base station. The instruments collect weather measurements and transmit them to the base station.
The base station is connected to an older e-Machine running Windows 7 Ultimate (32-bit) on 2GB RAM.
An Acurite application interfaces with the base station and the application stores data locally in a single CSV file.
I wrote a very simple C# console application named “abt” (an acronym for “Azure Blob Transfer”) to transfer the CSV file to Azure Blog Storage.
An Azure Data Factory pipeline that loads an Azure SQL DB staging table.
The AndyWeather website which reads the latest weather data from the Azure SQL DB staging table.
I wrote another very simple C# application named “awt” (an acronym for “AndyWeather Tweets”) that tweets updates to the @AndyWeather twitter account.
Acurite Weather Station
The latest iteration began in early 2018 when I purchased an updated package of instruments and a new base station made by Acurite. So far, I like this station a lot. It was less expensive than the previous station and appears more rugged (again, so far – time will tell).
I recently relocated the weather station to improve connectivity between the instruments and the base station. I recorded a Data Driven *DataPoint* about it:
(Pay no attention to the exploding pecans in the background…)
I intentionally use an under-powered PC for the server. Why? I want to learn how the base station – and then everything downstream of the base station – responds to busy server conditions. This is Engineering 101 stuff and I’ve learned a lot:
I love this old machine!
The Acurite people maintain an application for communicating with base stations:
The PC Connect application allows me to configure how and when weather data is collected from the base station – which collects measurements from the instruments. The application lets me configure the units-of-measure and file location – and I can even share my weather data with Weather Underground. How cool is that?
The Azure Blob Transfer Console Application
The Azure Blob Transfer (abt) application is a very simple console application written in C#. It picks up the CSV file containing weather data stored by the Acurite PC Connect application and writes the file to an Azure Blob Storage container:
The CSV file in Azure Blob Storage is overwritten each time abt successfully executes. You can download a copy of the abt solution here.
Azure Data Factory Pipeline
An Azure Data Factory (ADF) pipeline calls a stored procedure that first truncates a staging table in a Azure SQL DB using a Stored Procedure activity, followed by a Copy Data activity that copies the weather data from the CSV file in Azure Blob Storage to an Azure SQL DB staging table:
At the time of this writing, ADF version 2 is current.
You can download the ARM template for the pipeline here.
The AndyWeather Website
The AndyWeather website has been around since the days of the first iteration of AndyWeather – the one that stored data in a SQL Server instance hosted at GoDaddy’s DMZ. It’s fairly straightforward code, which helps it perform fairly on desktops and mobile devices:
The biggest performance hit comes from executing the stored procedure against an Azure SQL DB, which can sometimes take 5-10 seconds to complete.
The AndyWeather Tweets Console Application
I snagged some C# code and a TwitterAPI class from a project named called TweetSharp to help build the awt console application:
It makes me happy every time I see a tweet from @AndyWeather:
I tell people, “It’s just a dumb little app,” but I really had fun building it. I learned a bunch, too!
The AndyWeather IoT solution uses hybrid technology – on-premises instruments and servers, combined with cloud services – to deliver weather data to a website and Twitter account. It’s accessible from social media and the web from desktops and mobile devices.
Just so you know, this isn’t everything I’ve built using the AndyWeather instruments. There’s a bunch more – some of which is still in the experimental phase. I’ll share more as time permits. But I want you all to know, I consider Azure a great big cyber-playground!
I occasionally get questions about my companies (yes, plural) so I thought I’d write a post explaining them.
Enterprise Data & Analytics
Enterprise Data & Analytics is a boutique consulting firm. I own the company and I also deliver consulting services. We have a team of experienced engineers who deliver everything from cloud data migrations to data science to performance-tuning to tier-2 support for Azure, AWS, SQL Server, MySQL, SSIS, and other data-related platforms. If we don’t have the experience in-house, we can find someone who can help your team deliver.
Andy Leonard Consulting
Andy Leonard Consulting is the umbrella company for my other activities and includes:
My company with “consulting” in the name does everything except consulting. My company that doesn’t have consulting in the name does consulting. Confusing? Yep. So how’d I get here?
How I Got Here
When I decided to leave my last venture – actually, before I decided to leave, while I was having conversations about how to remain engaged there – I started Andy Leonard Training, Inc. DBA Andy Leonard Consulting. Shortly thereafter, I was having lunch with a friend who is way better at business stuff than I am and he said to me, “Andy, ‘Andy Leonard Consulting’ sounds like a one-person operation.” My response? “There’s a very good reason for that…”
My friend encouraged me to find a different name for my company. And then he found the name Enterprise Data & Analytics and even found the coolest URL: entdna.com.
That’s a good friend right there. And his suggestion was excellent; it wasn’t long before Enterprise Data & Analytics began to grow. Earlier this year, we (quietly) doubled in size.
Data Integration Lifecycle Management Suite
I kept Andy Leonard Consulting around because I wanted to keep software development activities partitioned from my consulting activities, in case my software development ideas failed. That may sound dumb today – three years after it started and with the success of DILM Suite and the book – but back then? I didn’t know if my ideas about DILM would ever come to fruition.
I’m pleased as punch to say now, three years later, that my ideas have produced fruit in the form of several utilities that make up the Data Integration Lifecycle Management Suite:
Catalog Reports – a free and open source collection of SSRS reports for the SSIS Catalog.
SSIS Catalog Compare – the flagship product of DILM Suite, Catalog Compare manages enterprise SSIS Catalogs on-premises and in the cloud! SSIS Catalog Compare can be used to lift and shift SSIS to the cloud, even.
Biml Academy was started to host videos from a week of one-hour free training sessions delivered in 2016. The idea was wildly popular and a team of Biml People delivered even more weeks of Biml Academy training. The free videos and content from those deliveries of Biml Academy remain on the site and remain free.
Knowing this doesn’t help; I still keep a few pet peeves. One of my pet peeves is this statement, “You don’t know what you are doing.” Why is this a pet peeve? It denies the obvious fact that everyone one of us, everywhere, is still learning.
“My Name is Andy and I Own and Operate a Consulting Company.”
“But Andy, you don’t know how to own or operate a consulting company.” That may or may not be a true statement. What is a truer statement? I may not know everything there is to know about owning and operating a consulting company, but I can learn.
“My Name is Andy and I Built a Software Product.”
“But Andy, you don’t know how to build a software product.” That may or may not be a true statement. What is a truer statement? I may not know everything there is to know about building a software product, but I can learn.
Interesting sidebar: SSIS Catalog Compare is not only the first product I’ve ever written, it’s the first complete application I’ve written in C#.
“My Name is Andy and I Co-Host a Successful Podcast”
“But Andy, you don’t know how to co-host a successful podcast.” That may or may not be a true statement. What is a truer statement? I may not know everything there is to know about co-hosting a successful podcast, but I can learn.
What is someone truly saying – what do they truly mean – when they say or write someone doesn’t know what they’re doing?
They’re making this statement about themselves: “I couldn’t so you shouldn’t.”
No one brings this point home better than Grant Cardone in his book (get the audio book – you are welcome), Be Obsessed or Be Average, or #BOBA. The followup to his (awesome) book, The 10X Rule, Be Obsessed or Be Average complements and completes Cardone’s thoughts on the hard work and time required to achieve success.
“What is the Point, Andy?”
When people make statements like “You don’t know what you are doing,” they are saying, “I gave up so you should give up, too,” or, “I didn’t get what I wanted so you don’t deserve what you want, either.”
This is very fair thinking.
When I write the word “fair” I shudder at what “fair” has come to mean and how it’s been used to justify junk and the crap it’s been used to rationalize.
I am not going to quit learning. I will continue to try to make old things work better. I will continue to try new things. I will fail more often than I succeed (this is how I learn). I will not stop until I go home.
My advice, encouragement, exhortation:
Make the problems give up before you do.
Listen to people who have succeeded (or are succeeding).
I see – and have lived – this virtuous cycle in the SQL Server and PASS communities:
A person discovers the Community and is overwhelmed at our openness and genuine willingness to help others. They realize they are not alone.
They learn more and become better at their jobs which, in turn, positively impacts their quality of life.
Some desire to give back to the community, so they develop a presentation and submit it to a User Group or SQL Saturday.
Some are selected to deliver their presentation.
Some presentations are well-received and increase the visibility of the presenter in the community.
As presentations are honed over time, some are used as a springboard to develop and deliver other presentations, further increasing the visibility of the presenter.
Some presenters achieve enough visibility to become a brand.
Some presenters are selected to present at larger events, like the PASS Summit.
Some presenters use their newfound greater visibility and brand awareness to join a consultancy practice or to become independent consultants.
The continued care and feeding of the brand of some consultants drives business growth.
The businesses of some consultants grows to the point where they can become sponsors and exhibitors at events such as User Groups, SQL Saturdays, and – eventually – the PASS Summit.
This cycle can be broken (or quashed) at any point by any number of actions, inactions, missteps, mistakes, and/or competitive overreach. In fact, I promise you will make mistakes and take missteps along the way (ask me how I know), but those mistakes and failures can tear you down or build you into more than you were – and the outcome is 100% your choice.
I advocate for the next generation of presenters. I want to see you engage, learn, share, grow, build their brands, and give back – just like I did.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.