A place to find insight into SQL
For those of you who missed out on the free day of training at Jacksonville SQL Saturday (May 4th), there is always next year. My session was on ‘Dynamic Duo: Window Functions and CTEs’ . You can scroll down to the bottom to see the Power Point presentation. I’ve included a few pixs from the day as well.
Needless to say, it was a Star Wars theme and the speakers were dressed up as Star Wars characters; I came as Princess Leia:) Hope you enjoy the Power Point and pixs!
After speaking at SQL Saturday Jacksonville this year, I realized some folks might not know where to go and download a free developer version of SQL Server 2016, or that SQL Server Developer edition has the full functionality of an Enterprise edition; it’s a licensing thing not a functionality thing. Let me show you how to get up and running with SQL Server 2016, and learn some fun stuff in the process. I’m back from my blogging hiatus and we have much to cover.
It’s been a while since my last blog post; life and hurricanes have kept me busy. But now it’s time to get back to training basics; so let’s start with how to download and install SQL Server 2016 Developer edition. With upcoming blogs, I’ll show you how to build simple SSRS reports, how to use the new Mobile Report Publisher, and introduce you to SQL Integration Services. Sprinkled along the way, we’ll talk about some fab free learning sites for deeper technical training; you’ll not want to miss anything.
First let’s go download your free, fully functional Developer edition from Microsoft; you’ll see three options. Click the ‘Download now’ for the Developer edition, and I recommended you open the link with an Edge or Chrome browser; sometimes when you use IE you get a strange warning. One other thing, you’ll need to have Windows 8 or higher to run SQL Server 2016.
If you’re uncertain you have the correct hardware or software requirements, click here for all the specifications.
Once you know your current computer/operating system can handle the installation, use this link from Wise Owl to show a step by step installation. This ‘SQL Server 2016 Part 1- Getting Started and Installing SQL Server 2016 Developer Edition’ is presented by the fab guys at Wise Owl in London, England; I love Wise Owl and I think you’ll like their free training too.
By now you should have SQL Server 2016 installed on your computer, so what about a sample database? In times past, if you wanted to download the trusty AdventureWorks databases, you went to CodePlex; but alas, CodePlex is being fazed out, so head to this Microsoft link to get sample databases and scripts for SQL Server 2016.
Okay, enough for today. In the next blog we’ll become familiar with SQL Server Management Studio (SSMS) and write some code together. These code scripts will be our starting point for building out some simple reports in upcoming blogs. Let me know what you think, and I’ll see you next time.
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
A personal ‘thank you’ to everyone who attended my session at SQL Saturday Jax yesterday; your excitement about starting (or continuing) your technical journey was inspiring. A special ‘thanks’ to those who personally took the time to tell me that they were encouraged, that they could create their own technical path to ‘fully employed’ as a result of the session. I am truly humbled by your gracious comments.
The slide deck for the presentation is now loaded to the SQL Saturday website: scroll down the page, find my session, and you’ll see the download option. In addition, I’ve included the slide deck with this blog post as well. Three Foundational Bricks PDF format
Many of you took the time to complete the session survey, thank you for your positive feedback! There was one comment that caught my eye, and I want to address it briefly now, and hope to go into more detail with another blog post. The survey comment asked the question ‘how to become fully employed but no work experience’.
The focus of yesterday’s talk was to provide you resources to get you started in the learning process, but the ‘experience’ question is a good one. Until I can address this in detail with a future blog post, I’ll leave you with two ideas. After you’ve learned your skills, volunteer your technical skills at a non-profit organization.
Non-Profits
Small non-profits usually cannot afford much IT support, so you volunteering your services would provide them with the technical services they need, and provide you with the ‘real-world’ experience you need for your training (and your resume).
Create a Portfolio
Should any of your training incorporate Power BI, you can show your reports and dashboards live on your Power BI site (which is free for a personal account). Also, create some SSRS reports and capture them with screenshots; you can then embed the screenshots as part of your growing portfolio.
Let me know if delving further into this is of interest to you, and we can explore this in greater detail. I would love to hear from you, so feel to leave comments/questions on what you’re interested in hearing about. I’d like to explore Power BI Desktop and T-SQL basics you need to know in the coming weeks.
Before I close for today, you might want to sign up for IT Pro Camp on June 11th at Keiser University, click here for more information. 
So thank you for taking this technical journey with me as we continue becoming ‘technical’ together. And consider signing up for new blog notices by subscribing at the Subscribe@ section to the left. Until next time!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Several of you have been asking why there has not been a blog post of late. Well, I took a full-time job last year as a BI Analyst, so my time has been limited; however, there are so many exciting things happening in the ‘tech universe’, I could wait no longer. Needless to say, it’s great to be back!
Microsoft is ready to unveil SQL Server 2016, and the changes to SSRS are set to be major. In fact, if you are in the Jacksonville area on April 20th, Brad Gall is speaking at JSSUG on ‘What’s New in Reporting Service 2016’; for more info, click the link at the end of this blog. And Power BI……the Power BI team has put out new features on a monthly basis. In fact, the landscape of Power BI has changed so dramatically over the past 7 months, you would be amazed.
My last blog left off with the promise of exploring Lynda.com Part 3 on Power BI; but things have changed so drastically with Power BI, the Lynda.com courses we were set to explore are obsolete for the most part. There have been 13 monthly updates in Power BI Desktop alone; check out the link at the bottom.
So you see, unless you’re following Microsoft directly or watching the newest Pragmatic Works free ‘Training on the T’s’; it’s a race to keep up with the technology. How fun is that! Well that leads me to more good news; SQL Saturday Jacksonville is happening next month, May 7th, on the campus of University of North Florida. I have included a link with information; it is a free, full day of technical training. You will definitely want to sign up and spend the day with us.
There will be 9 different tracks, with over 40 sessions; and there will be 3 Pre-Conference sessions on Friday, May 6th. The Pre-Con sessions will cost you a little money, but it is well worth the price! The 3 Pre-Cons are:
-A Day of DBA Fundamentals: Install, Maintenance Plans and Security
– A Day of Azure
-Introduction to Data Science
If you can’t make a Pre-Con session on Friday, definitely sign up for SQL Saturday, it is totally free. Should you be able to attend SQL Saturday, I hope you will consider attending my session at 10:15 entitled: ‘From Non-Technical to Fully Employed Technical: The Road Less Traveled’.
It’s hard to believe that since beginning my technical journey 6 years ago, I now have the privilege to speak at the same SQL Saturday that started me on my journey to technical. I hope you’ll consider joining me on May 7th as we continue becoming ‘technical’ together. Until next time!
Jacksonville SQL Server User Group (JSSUG)
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Sometimes it’s hard to envision the finished product of your labors when you’re in the middle of the construction mess. And it’s hard not to become frustrated when you can’t instantly learn all the great technical stuff out there. Well, you’re heard that Rome wasn’t built in a day, and neither will your technical studies. Building a solid technical foundation takes time and a ‘no quit’ attitude. If there was another way to do it, I would have found it by now! So let’s continue our journey as we explore Excel, an essential part of a solid foundation.
Excel 2013 Focus with Lynda.Com
I hope you have completed some of the courses we discussed in Lynda.Com In-depth: Part 1; if you have not read the post, click here. For those of you who have completed the courses suggested in Part 1, you’re now ready for focused courses on Excel 2013. But why Excel, when we’ve been studying SQL Server? Because Excel is the preferred ‘go to’ for business analysis by the vast majority of companies. Managers and owners still like the ‘slice and dice’ of Excel, so you must know Excel as well. It will also set a solid foundation when you move into Power Pivot!
Building a Solid Foundation
Lynda.Com has a plethora of Excel courses, so we need to limit the scope. Today’s post focuses on five courses; they teach essential and advanced concepts, so let’s take them in the best order for learning. The great place to build a strong foundation is at the bottom, so Excel 2013 Essential Training is where you should begin. Even if you have a good understanding of Excel, this course will serve as a refresher while introducing you to additional features found in the 2013 edition.
Excel 2013 Essential Training is 6 ½ hours long; most of the ‘Essential’ course topics taught on Lynda.Com are lengthy but worth the time. After completing the Excel 2013 Essentials you will be able to enter and organize data and build workbooks. You will be introduced to basic functions, formatting, creating simple formulas, sorting data and more; in addition to an introduction to pivot tables. Now keep in mind, a pivot table is not the same as a Power Pivot table; the business intelligence aspect of Excel 2013 will be discussed in the third part of Lynda.Com.
Next Level Up
Once you’re waded your way through the Essentials course, you can move on to Excel 2013: Tips and Tricks course. This course is 4 ½ hours and shows you how to increase your productivity with ‘power user’ tips; teaching you how to create formulas quickly, split data into columns, apply formatting, build charts, and create split screens quickly. There is also a chapter on ‘Top Ten Shortcuts’ and ‘Ten Tiny Tips’, if you don’t have time for the full course. But before you move on, watch the section on Database Techniques with ‘Flash Fill’ feature. Now you can move on to Excel 2013: Power Shortcuts.
Excel 2013 Power Shortcuts is a mere 3 hour long course showing you over 65 tips, tricks, and shortcuts. Some of the material mirrors the previous course, so compare and contrast to see which one you’d like to watch. The Power Shortcuts topics cover creating hyperlinks, setting print areas, removing duplicates, analyzing a formula for errors, and summarizing data with charts and tables.
Higher Level
The last two courses deal with more complex subjects: Data Validation and Time/Data; both are considered Intermediate level courses. Excel 2013: Data Validation in Depth, only one hour in length, shows how to control data input into workbooks by controlling dates, length of text, types of data allowed in a workbook, using an error alert tab, and more. The focus of this course is to ensure your data is accurate and valid.
Our last course for review is Excel 2013: Working with Data and Time. This one hour course explains how Excel stores and uses dates and times and how Excel stores dates and time. You’ll learn useful date/time functions to customize your dates, format dates over a 24 hour period, and calculate differences across dates and times. A solid understanding of dates and times will greatly enhance your skills when you work inside a Power Pivot model as well.
A Few Final Thoughts
Next time, we’ll conclude the Lynda.Com In-depth with a look at the Power BI courses for Excel, with emphasis on Power Pivot, tables, and charts. This is where the fun really begins! You will not want to miss it.
I’m reminded of a saying my sailing captain would often say: “When you get to the dock, you’ve already arrived at your destination.” Captain Kevin wanted to convey that the trip was not about getting to a location, but enjoying the journey along the way. So thank you for taking this technical journey with me as we continue becoming ‘technical’ together. Until next time!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
I can’t even begin to tell you how many times this error has popped up on me; it’s like the ‘Dread Pirate Roberts’ from the movie ‘Princess Bride’, you never want to meet him! But I have great news, I have a semi-quick fix for the issue. I will give you the ‘down and dirty’ way to fix the problem, because you’re most likely in the middle of a project and need the solution NOW. But, if you’re interested to find out WHY the error occurs, keep reading. There will also be a link at the bottom to a great article by Guy Glantser called “How to Load Data from Excel in SSIS -32 bit vs 64 bit’” and how he fixed the dread error message. His article provided me the solution!
If you encounter this error you’re most likely working in a 64-bit environment (SQL Server, Excel, etc) and using a 32-bit tool like SQL Server Data Tools (SSDT). There is no 64-bit version of SSDT; it doesn’t exist so just get used to it:) By now, you should know that 32-bit and 64-bit do not play well together. That’s probably why Microsoft makes 32-bit application installations the default setting, and why you must go out of your way to load the 64-bit version. Don’t even get me started about setting up Office 365 in 64-bit mode; that’s a rant for another day!
Quick Steps
You’ll need to uninstall the Microsoft Access Database Engine 2010 Redistributable 64-bit version from your machine and download and install the 32-bit version. Click here to get the download. After you have the 32-bit version installed, you’ll need to make a few changes in your SSDT environment.
Under the Project tab of your current project, click the Project properties at the bottom; in my case, the project I’m working on is called Wise Owl SSIS Project. Click on the Project Properties (see screenshot) and change ‘Run64BitRuntime’ to FALSE’.
Last thing to do is go to the Properties box for your Package (in my case it’s called ‘ConditionalSplitLes7’ package), and change the ‘DelayValidation’ to TRUE.
Now run your package and you should start seeing little green checkmarks. Happiness is Little Green Boxes!
How and Why
If you’re like me, I’m not completely satisfied until I understand the reason behind the error and why the fix works. So hang with me a little longer for the details; I hope you’ll leave happier and wiser.
You already know that the problem stems from your 64-bit environment being forced to deal with a 32-bit application, specifically SSDT. Your Excel connection manager wants to use the ACE.OLE DB provider; but that pesky error keeps popping up, the ‘ACE OLE DB provider is not registered’. But remember, your ACE.OLE DB provider is 64-bit AND you’re trying to interface with a 32-bit SSDT. So this is why you had to uninstall the 64-bit and install the 32-bit version of ACE.OLE DB provider. Now you have 32-bit Provider with 32-bit application; that’s why you made the next change: ‘Run64BitRuntime’ to FALSE. When this property is set to FALSE, it allows the package to run in 32-bit; everyone is playing well.
But you still want to run the package in 64-bit, but there are validation issues. Let’s look at what’s happening beneath the covers. According the Guy Glantser’s article, the SSIS package goes through a validation process BEFORE the package execution ever occurs. This preliminary validation is to ensure everything is configured properly before execution.
Since you have the 32-bit provider installed, you can pass the first validation hurdle, but the package will fail upon actual execution when you try to run the package in 64-bit mode. The workaround was to make the last change by switching the ‘Delay Validation’ to TRUE. By selecting TRUE, your task will be validated at run time; and as the package is controlled by the ‘Run64BitRuntime’, your package is executed in 64-bit! See all my little green boxes!
Hopefully we’ve seen the end of the ‘ACE.OLE DB.12.0 Provider not registered’ error. So long Dread Pirate Roberts!
Here is the link to Guy’s article
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Before I sat down to write this post, I reviewed the Lynda.Com website to refresh my memory of courses taken and courses I want to take. Forty minutes passed before I looked up from my screen; so many great courses, so little time! Today we’ll break down the plethora of Lynda.Com training options over several blog posts: Part 1-SQL Server and T-SQL, Part 2-Excel 2013, and Part 3-Microsoft Business Intelligence/Excel 2013 Power BI. Other Lynda.Com courses on ‘building your brand’: Branding Basics, LinkedIn, and WordPress for Blogging will be examined later in the overall series. For my previous post on Lynda.Com basics and free trial information, click here to read. So, if you’re ready, join me in exploring Lynda.Com courses on SQL Server.
SQL Server
SQL Server is the backbone of the Microsoft BI stack: SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services [Multi-dimensional and Tabular flavors]), and SSRS (SQL Server Reporting Services); with everything ‘talking’ to each other via T-SQL (Transact SQL) language and DAX (Data Analysis Expression) language for Tabular and PowerPivot in Excel 2013. To help you build a solid foundation, I‘ve listed several Lynda.Com courses with their time length and subject matter. You don’t have to follow my order, but it will probably help you from getting lost and ‘falling down a rabbit hole’.
At the time of this writing, it’s hard to find the SQL Server 2012 Developer Edition for $59, but you can still purchase the SQL Server 2014 Developer version for around the same price. So purchase SQL 2014 Developer Edition if you don’t already have a SQL Server instance, so you can follow along with the Lynda.Com SQL Server 2012 course without any problems.
Installing SQL Server
The first course to watch is Installing SQL Server 2012; it’s a three hour long course. Not that you’ll be able to get through it in three hours, as you will want to stop and rewind the video, take a few notes, and actually practice the examples on your own SQL Server Developer Edition. So it will be helpful to have an extra monitor for these classes; one to view the video and another screen to work with your SQL Server application. If you have not purchased your SQL Server Developer Edition for 2012 or 2014, this would be a great way to watch the video and install your software at the same time!
Installing SQL Server 2012 teaches you how to install and maintain your SQL Server application, either from the GUI (Graphical User Interface-it’s like an installation wizard) or from command prompts. This course also shows you how to configure and enable the different components of SQL Server, and how to connect to databases.
The Language that Makes SQL Server Run
The second course I suggest is Introduction to Transact-SQL; it’s over fours long but you’ll need every minute of it. This course is a good introduction to T-SQL, but the best way to learn T-SQL is from Itzik Ben-Gan books; Ben-Gan is a T-SQL guru to me. In an upcoming blog I will talk about my favorite training books and authors, but if you can’t wait until then, I recommend Ben-Gan’s book ”Microsoft SQL Server 2012 T-SQL Fundamentals”.
With the Lynda.Com course on Transact SQL, you will learn how to use the SQL Server Management Studio (SSMS), basic command statements like SELECT, inner and outer joins, and more. Another Lynda.Com course, Querying Microsoft SQL Server 2012, will build on the previous T-SQL course.
Querying Microsoft SQL Server 2012 is over five hours, and includes a history of SQL and the relational data model theory. From here you’ll build your skills to include working with special data types like characters, integers, time and date. You’ll be exposed to advanced topics like subqueries, common table expressions, and query performance.
If you have the SQL Server 2014 Developer Edition, this next course SQL Essential Training (3 ½ hours) has been updated for SQL Server 2014. The course provides a good understanding of SQL terminology, writing basic queries, sorting and filtering, along with updating a table with triggers. Creating views is also included in this course. These courses will give you a good overlay of T-SQL, but I still recommend you buy the Ben-Gan book; yes, the book is just that good!
So What’s Next?
If you have completed the above courses, you now have a basic understanding of the SQL Server application and know the basic T-SQL commands, but these two don’t paint the full picture; you need to understand the concepts of a relational database, so I recommend another Lynda.Com course, Relational Database Fundamentals. To be a good developer or report writer, you must know about relational databases and, well, ‘relationships’! SQL Server Reporting Services in Depth is an advanced course on Lynda.Com, but it’s a good way to show how to build static reports from a relational database.
Final Thoughts
I’ve provided you several excellent training courses through Lynda.Com, but they might seem overwhelming. Where do you find the time? I remind you of the old saying, “How do you eat an elephant? One bite at a time”. This is a good time to remember this saying. You will not become technical overnight, but you WILL become technical; you just need to remain steady in your studies.
We’ll continue exploring other great courses on Lynda.Com for the next few posts, so stay tuned. Until then!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Note: ‘Technical Tidbits’ is an addition to this blog comprised of mini-blogs on different technical topics. Not all of these topics will be on the introduction level; in fact, most will deal with new things I’m learning as I continue on my own technical journey. These little blogs will expose you to advanced topics. You will want to read them and tuck them away for later review. For example, you may not have a clue about parameter tables now; but when you begin your study of the DAX language, you will remember the ‘tidbit’ blog and have it to review. Think of ‘Technical Tidbits’ as little markers down the road; they’re showing you what lies ahead.
DAX Master Wanna-Be
I’m on a quest to be a DAX Master. As Yoda of Star Wars fame wielded the ‘force’ as a Jedi warrior, I’d like to wield DAX functions to make Tabular Services and Excel Power BI slice through complex evaluation contexts! OK, I want to be taller and cuter than Yoda as well; I’m a stickler for detail.
So what is DAX and what is so great about DAX? Data Analysis Expressions is a formula language, used primarily with Tabular Services and Excel Power Pivot data models. Using DAX, you can create calculated columns and calculated fields on your data models; but our focus in ‘Technical Tidbit’ today is on creating a Parameter Table.
OK, what do DAX and Parameter Tables have to do with each other? What is a Parameter Table and what is the value of creating one? Well, you might not get as excited about Parameter Tables as I did, but I thought they were amazing when I first learned about them. As I read Ferrari and Russo’s book Microsoft Excel 2013: Building Data Models with PowerPivot, Chapter 7 dealt with ‘Understanding Evaluation Contexts’. Then, in order to bring Evaluation Context into the practical realm, they set up a real-world scenario utilizing the theory of evaluation context and introduced Parameter Tables.
I won’t go into the concept of ‘evaluation context’, but remember that it is VERY important to understanding DAX; using it wisely with data models and filtering is a must. With that said, let’s get to what a parameter table is and when they might be useful. A parameter table is when you create a little Excel table BUT it is unrelated to the rest of the data model. Well that doesn’t do anybody any good; but wait! This unrelated parameter table is used internally by DAX functions to modify the behavior of the other tables.
Say, for instance, you have a database with millions, billions or trillions of rows of Sales Amounts; trying to build a report on the sales column could drive you nuts. Now say your manager’s boss wants to see a report on all these sales. This amount of data would take up lots of space in a report and would not lend itself to concise reporting or easy analysis. We need a plan; a parameter table!
How to Create a Parameter Table
Your parameter table (let’s call it ‘ParameterSlicer) will allow the end user to decide the scale they want to view the sales amounts; for example: ‘Real Value’, ‘Thousands’, ‘Millions’, ‘Billions’, etc. The use of the parameter table does not filter the data; but allows you to change the scale used in viewing the numbers. Now that’s slick!
First, I used the ContosoRetailDW to create my data model, especially because there are millions of rows with lots of sales amount that will produce large sums. Then I created a little Excel table, named ‘ParameterSlicer’; giving it three columns ‘ShowValueAs’, ‘DivideBy’, and ‘Order’ along with four rows: ‘RealValue’, ‘Thousands’, ‘Millions’, ‘Billions’.
After you create the new table, you used the Table Import Wizard to load the Excel table to your data model. Remember, the new table is not related to anything inside your model; but once the table is in the model, you can use it as the source for a report slicer. This little Excel table will be the source for the slicer in that report your boss wants to see; but nothing will happen without the DAX Magic!
DAX
Without DAX, your little Excel table will sit in the data model and just look cute; lonely and unrelated, but cute. 
But that’s not good enough; we must use DAX code to make everything work. Your DAX formula must ‘see’ what the end user has selected in ‘ShowValueAs’ and modify the content of the report to display the values selected. I modified the DAX calculated field as shown in Ferrari and Russo’s book, and will try to explain it as simply as I can.
SumOfSalesAmount (this is just the name of your calculated field that was placed on the ‘Sales’ table) =
IF (
HASONEVALUE = ( ParameterSlicer[ShowValueAs]), (this is the table name|table column of parameter table)
SUM (Sales[SalesAmount]) / VALUE (ParameterSlicer [DivideBy]),
SUM(Sale[SalesAmount])
)
DAX Explained
The IF function works with another function HASONEVALUE. The IF function test a condition; if the end user does not select anything in the slicer, the HASONEVALUE will return FALSE and nothing happens. When the end user selects a single value on the Slicer table, then the HASONEVALUE will return TRUE and the sales numbers will be seen by the scale selected by the end user. It’s like saying “if the user has selected a single value in the slicer, then show the ‘sum of sales amount’ divided by the corresponding denominator; otherwise, show the total of the ‘sales amount”.
It’s important to remember that the ‘ParameterSlicer’ Excel table DOES NOT filter the data, it will simply allow the end user to change the scale of the numbers. Now with the Excel table loaded into the data model and this DAX calculated measured placed on your ‘Sales’ table, you can bring everything together. 
This is a simple example but the most common use for a parameter table; however, they can be used for much more. With a parameter table you can modify the way a number is computed, change parameters for an algorithm, or change values returned from a calculated field. So don’t dismiss this little guy.
Final Thoughts
The table names and columns have been changed to protect the innocent, just kidding; you know you’ll need to plug in your actual database table names and columns, but you get the idea of using parameter tables. Be sure to get Alberto Ferrari and Marco Russo’s excellent book: Microsoft 2013: Building Data Models with PowerPivot. The book is a great way to begin your journey into DAX. Until next time!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Deep Dive: Pragmatic Works Training
I’ve blogged about the great training at Pragmatic Works before, but today, we deep-dive into the various training available through Pragmatic Works; let the lovefest begin with Training on the T’s!
Before my journey on the technical side, I was a teacher; so it’s quite easy for me to recognize great teaching versus good teaching. So, if you have a limited number of hours to dedicate to training, but you want to get the best possible learning experience; then you need to watch Devin Knight’s training sessions. It doesn’t matter what Devin is teaching on, the subject is going to be excellent! Devin is a wonderful communicator and teacher, and he presents the subject matter in a logical progression; making it easy to learn.
Another favorite presenter is Brian Knight. I remember sitting in a seminar at a SQL Saturday, many years ago, listening to Brian talk about Data Mining. Brian’s knowledge of the subject, along with his boundless enthusiasm, made you want to learn Data Mining. He says data is sexy, and he’s correct!
I’ll also introduce you to a few more of my favorite webinar presenters: Kathi Kellenberger, Bradly Schacht, Mike Davis, and Angel Abundez. There are other great presentors like Adam Jorgensen, Shawn Harrison, and Jason Strate; but there’s not enough time in the blog post to cover everything!
Building a Strong Foundation
For the next few minutes, I want to list specific Training on the T’s webinar titles and presenters that will help you build a strong foundation in your technical knowledge. I’ve selected the following order of webinars to save you time and help you lay a solid foundation for your learning. So let’s get started!
First off, watch Devin Knight’s webinars, with my comments listed for a few of them:
-Understanding Microsoft Self-Service Business Intelligence
-Introduction to Power BI (**great overview of Power BI Excel 2013)
-Introduction to Power Pivot (**the cornerstone to the Power BI tool suite in Excel 2013)
-Introduction to Common DAX Expressions (**the language that make Power Pivot rock)
-Creating Real World Power Pivot Models (**you’re only as good a your database)
-Introduction to Power Query (**talk about an easy way to ETL: ‘extract, transform, and load’)
-Creating an End to End Power View Reporting Solution (**deliver the ‘wow’ in report presentation)
-Touring your Data with Power Map
-Choosing a Microsoft Reporting Technology
Now, there are many more topics that Devin has available, but I’m giving you a logical course of training to give you the building blocks you need for a solid foundation in the MS BI Stack. After you view these webinars, let’s move on to a few webinars from Brian Knight.
One of the best ‘soup to nuts’ overview on planning a Data Warehouse was recently presented by Brian (with PowerPoint by Devin). The webinar presents a clear cut look at the planning stages of a DW. With that said, it’s also beneficial for beginners as well, as you learn the terminology and concepts; you’ll not want to miss this one.
Brian Knight:
-Tips and Tricks for Planning a Data Warehouse (**great presentation ‘soup to nuts’ overview)
-Quick Start to Power Pivot
-How to use Power Query as your Self-Service BI ETL tool
Topics for a Future Day
When you have some more training under your belt, you’ll want to move on to webinars by Mike Davis, Kathi Kellenberger, Bradley Schacht, and Angel Abundez. When you feel comfortable with T-SQL, learn about Window Functions; Kathi gives two excellent webinars on this subject. Mike Davis gives the best presentation on how to prepare for a technical interview. His presentation on the subject at the 2014 SQL Saturday was excellent, but his webinar ‘Mastering the Technology Interview’ is even better! These folks are just a few of my favorites, but all the PW webinars are excellent. When you get a chance, check out some webinars by Adam Jorgensen, Shawn Harrison, and Jason Strate as well.
Kathi Kellenberger: T-SQL Window Functions
-T-SQL Window Function Performance
-Write Better Queries with Window Functions
Mike Davis
-Mastering the Technology Interview
-Complex DAX Expressions Power Pivot
Bradley Schacht
-Introduction to ETL Using SSIs
-SSIS ETL for Beginners
-Back to Basics: SSIS 2012 for Beginners
Angel Abundez
-Power BI Tips for Data Analyst
-Chart Anatomy 101
Virtual Training
Sometimes you just need to spend some money on training. So when you’re ready to make that move, PW offers Virtual Training courses in six areas: Analysis, Big Data, Business Analytics, Data Integration, Data Visualization, and Database Administration. The cost is $995.00 for 4 days (3 hours each day) of in-depth virtual training; the class is limited to around 24 participants. You’ll usually have labs to complete after each day’s training. For more information click on the Data Sheet (a one page outline of each day’s topics), Details (specifics for each day’s training), and Date/Time(when the class will be offered during the year). I’ve taken the SSRS course and I’m scheduled to take the Tabular and Power Pivot for Developers in October. I can hardly wait!
Workshops and Bootcamps
PW offers workshops around the country; they are in-person two day training courses on a variety of SQL Server topics. Then you have Bootcamp, a week-long intensive learning on either Business Intelligence or Performance Tuning.
Closing Thoughts
Pragmatic Works has lots of good stuff under their Resource section; things like Articles and Whitepapers, Cheat Sheets, and more. If you are limited on time, but want consistent, excellent training; spend your time on the Pragmatic Works website. Well, we’ve definitely had a Pragmatic Works lovefest today. Next time we’ll take a deep dive into Lynda.Com and the great technical training available there. Until then!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!
Welcome back to part 2 of ‘Free and Low Cost Computer Training’. I decided to break up the blog because I wanted you to have time to explore the different sites at your leisure and to not overwhelm you with too much new information. Also, by the time we dive deeper into each of these sites, you will have had ample time to become familiar with them; so you’ll get more out of the blog posts. So let’s check out part 2 as we look at SQL Server Central Stairway’s tutorials and Lynda.com. If you missed Part 1 of ‘Free Training Options’, click here to read it.
SQL Server Central Stairway
SQL Server Stairway is another free training site, offering series of tutorials with detailed training on different topics. The mission statement of the Stairways series is so well written, I’ve included part of it to give you a flavor for the site; notice the emphasis to take you from “zero knowledge of a particular SQL Server topic, to a level of practical understanding… (the) learning gradient is steady and manageable, but also brisk. You won’t be wasting your time.” That’s what I’m talking about! Read the entire statement and see the different ‘Stairways’ of studies.
Here are a few of the ‘stairways’ available for immediate consumption: Stairway to Data, Integration Services, MDX, Database Design, PowerPivot and Dax, SQL Dialects, Powershell, SQL Server Agent, Reporting Services, T-SQL: Beyond the Basics and more. You’ll need to create a free account for this site as well. So what’s stopping you; ‘start climbing’!
Lynda.com Website
Lynda.com is the only site I’ve listed that charges on a monthly basis, but it’s worth every penny of it. Membership is $25 per month for 24/7 access to hundreds and hundreds of topics. Lynda.com covers so much more than just ‘technical stuff’; it has courses in Business, 3D, CAD, Design, Developer, Photography, Video, and Web; along with project management, WordPress, etc. This list is extensive, so I won’t try to list everything; check out the site for an overview ; click here for Lynda.com.
Free 7 Day Membership with Lynda.com
When you click on this link, you will be able to try Lynda.com free for 7 days, with unlimited access to over 2,700 courses.
If you like, sign up for the free 7 day trial subscription to get a flavor for what’s available. I’ll do a ‘deep dive’ on Lynda.com in upcoming blogs as well; so you can check the site out now or wait until we talk about it in depth. Regardless, you now have lots of training options!
Personal Suggestions
I would recommend you start with watching the past webinars on Pragmatic Works as a first start. These webinars are only an hour in length and many of them are targeted for the beginner. Personally, I would wait on taking a MVA course until you’re further down the road; these 6-8 hour videos can overwhelm a beginner if you’re not careful. The same might be said of SQL Server Stairways, but you can view some of them and make your own call. Regarding Lynda.com, review the site and sign up for the free membership when you have the time; you’ll want to make the most of those 7 days!
Depending on what courses you decide to take first, you might need to spend a little money on software. In the next blog, we’ll talk about what software you’ll need for what studies and where to find them for the best price. If we have time, we’ll discuss the Office 365 options available and the new prices Microsoft will offer starting October 1, 2014. Until then!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!