The core application my current company has built allows people to apply for grants. We track the application through various stages, much like a loan application there are many stages that the application might go through. The status of the application is not linear. So the applications can go back and forth between the same status more than once. In figure 1 below an application .can given a status of ‘under review’ then get a status of ‘submitted’ or ‘Pre-Submission’. So, an application might have the same status more than once with different dates.
Figure 1 –showing the workflow an application might take.
One question which our customers want to know is how long an application takes to move from one status to another. This question could be answered using the existing application database. Using that method is not ideal as the queries are quite complex and slow. This is one of the reasons I decided to build a Datawarehouse as a proof of concept. To see if there was a quicker way to answer that question.
Calculating time difference
Given that an application can be allocated a status more than once during its lifecycle. This has an impact on the method we used for calculating the time difference. The methodology used to get the time difference is defined below
The fact table in the POC Datawarehouse, stores the ApplicationID, Status, and the datetime the application was allocated that status. If an application has been assigned the same status then there would be a record for each time the application has received that status. This has some advantages; the end user can easily understand the data as it is shown in the table. On the other hand, this presented some difficulties with calculating the time period between each status.
After various attempts the following calculated column was created. In this case calculating the days between a grant receiving Pre-Submitted status and Rejected status.
Figure 2 – First version of the calculated column DaysFromSubmittedToRejected
For both values the logic used is very similar, for the variable FirstSubmittedDate the MIN() function is used to get the first date. The FILTER statement only returns records with same ApplicationID and Status = ‘Submitted’. Whereas the variable LastRejectedDate the MAX() function is used to return the last date. Using variables, means the logic can be re-used for other statuses, and the DATEDIFF() function is easier to read. When the results are displayed they were not as expected.
Figure 3 – results from the first version of the DaysFromSubmittedToRejected function see Figure 2
The requirement was to return the number of Days elapsed From Submitted To Rejected with the value returned against the record which is the last time the application received that status. So, another function was created to see if the current record is the LastRejectedDate, which is shown in Figure 4.
With this calculated column (LastRejectedDate) in place the return statement of ‘DaysFromSubmittedToRejected’ function just requires a small change which is as follows
VAR FirstSubmittedDate =
MIN ( 'FactAppStatuses'[DateAddedAt] ),
ALL ( 'FactAppStatuses' ),
'FactAppStatuses'[ApplicationID] = EARLIER ( 'FactAppStatuses'[ApplicationID] )
&& 'FactAppStatuses'[Status] = ‘Submitted’
VAR LastRejectedDate =
MAX ( 'FactAppStatuses'[DateAddedAt] ),
ALL ( 'FactAppStatuses' ),
'FactAppStatuses'[ApplicationID] = EARLIER ( 'FactAppStatuses'[ApplicationID] )
&& 'FactAppStatuses'[Status] = 'Rejected’
'FactGrantStatuses'[LastRejectedDate] = "Y",
DATEDIFF ( FirstSubmittedDate, LastRejectedDate, DAY ),
Figure 5 – Second version of the calculated column DaysFromSubmittedToRejected
When the function is added to the PowerBI report shown in Figure 5 then run then the results can be see in Figure 6 below.
Figure 6 – results from the second version of the DaysFromSubmittedToRejected function (see Figure 5)
The addition of the IF() statement uses the value to check if current row contains the LastRejectedDate. Otherwise it returns a zero. This returns the record set which is expected.
The positive uses of this method are that the same calculation can be re-used for a variety of statuses only requiring a minor change to the formula. On the negative side the more calculated columns there are the longer it would take to generate a report.
No doubt there are other approaches that can be taken to improve this. The time taken to evaluate the calculated column will be slower as the number of rows increases. On the other hand, the current method is significantly faster than doing the same calculations on the application database. Even better with the use of a PowerBI report or a tabular data model. These figures could easily be used by an end user to reports which provide insight into their data. All the better that our clients could do this for themselves, with relative ease.
On the the 10th of November 2017 the Glasgow SQL server user group ran #SQLGLA. This was a half day event, primarily for SQL server professionals in Scotland. Although we did have some visitors for further afield. Namely our four MVP speakers, William Durkin, Chrissy LeMaire, Andre Kamman and Rob Sewell.
Now the event is completed, we have gathered and compiled the feedback. Now I feel that there is time to think about the event. It's been a bit a rollercoaster ride, with the full complement of good bits and scary bits. Much of the thanks for making this entire event a success goes to my co-organiser Craig Porteous. Without Craig this event would not have come off at all.
It would be fair to say that both Craig and myself had visions of very few people coming. Other than the speakers, volunteers and ourselves. People did come and as often happens at these, people you did not expect arrived and those we expected did not arrive. All part of the learning curve. My memories of the actual day are, we arrived at the venue, we all ran up and down stairs, realized what we had forgotten to bring, set up the venue ready for our guests. People arrived, attendees, speakers, sponsors. Speakers, spoke, people mingled, chatted, had tea, coffee, sandwiches, then drank beer, & wine. We cleared up, said good bye. Some people head off to the pub to continue the fun. It was a fantastic day, all the objectives we had aimed for were achieved.
As we know these events do not run themselves. There is a dedicated team of selfless people who make these events happen. Craig Porteous my co-organiser put a massive amount of work into making this event happen. Even more his selfless decision to have one more drink at the SQL Bits party meant he met William Durkin, and the conversation resulted in the event :-). We were also very kindly blessed with two very enthusiastic and industrious volunteers Edith and Paul. Who always seemed to be in the right place at the right time, and just did what needed to be done.
This event was a huge learning experience for Craig and myself. This event combined with running the user group has taught me more than a few lessons. Some of which we have learned, some I am sure we are still learning. Now the event is over, and I have sometime to look back, I will blog about some of the lessons. About running a user group and a larger event such as #SQLGLA.
So first l created a variable in the query window see figure 1.
Now you have a card with dynamic title, if you want to change the client name all you have to do is change the text in the variable. A sample PowerBI report file can be downloaded here which contains the report shown above.
Whilst nothing can beat experience having a certificate can also improve the chances when looking for a new job. If an interviewer is looking at two evenly matched candidates, one without a certificate one with a certificate. Personally speaking, if l was the person making the decision then the person with the certificate would the one whom l would recommend getting the job. By studying and passing the exam the candidate has demonstrated, initiative, self-motivation, and a genuine desire for technology. At least that's my opinion. In one case l know of a colleague whom l work with, who said one the reasons they were offered a job was due to the fact that had studied and passed two exams, which were relevant to the role they applied for.
Craig Porteous spoke to some recruiters he knows and asked them some questions about their view of certification. So these thoughts are directly from those who make hiring decisions.
What weight do you put on certifications when hiring?
1) A lot. With two otherwise comparable candidates, the one with certifications wins in my mind.
2) A lot, think it shows that candidates are focused in developing their career.
3) For a technical role I see it as essential
Do you encourage the pursuit of certifications by your team?
Do you see any downsides/negative aspects to certifications?
1) No, none at all.
2) Some of the accreditation's could be more hands on focused.
3) Cost (retaining skilled up workers)
With staff who have completed certifications, do you see any differences in working practice etc to those who haven’t?
1) Yes. People working towards certifications are more engaged with technology and tend to apply their learning in the work environment, sharing their knowledge and improving the overall team dynamic leading to improved productivity.
2) It really depends on the individual so don’t think it’s a fair comparison. Better way to look at it is how doing accreditations adds value to that person in regards technical ability and confidence.
3) Last example was a infrastructure type. Stuck him on a sccm course. A,month later our sccm world upgraded. A year later he left and now heads up sccm at dell secure works in the US . For me a benefit – I get a sccm upgrade from a capable engineer. They get a badge they can use to get their career upgraded. Win win.
One reason l have heard for not doing any certification is the cost. My personal point of view on this is that l am investing in myself. If l learn a new skill gain or learn some new techniques. Yes, the company l am working for will benefit which is excellent news for them. If l choose to move to another role with a different company, then those skills transfer with me. Those skills l have invested both my time and (more importantly very often) my money in they are mine. So my choice is to invest in myself as l believe that the return on investment (ROI) is excellent.
So you have decided to study for certification, what resources are there available? The following list is just suggestions, based on largely on my experience and some others.
If you are planning to take the data platform exams then l would strongly suggest investing in the books for the relevant exam. For example, this one is for the 70-461 exam. The book covers all the topics that could be questioned in the exam. There is an accompanying CD has an electronic copy of the book and practice exam questions. One series of books which I used whilst studying for 70-461 exam Querying Microsoft SQL Server 2012/14 was series titled 2 Joes. The purpose of the books is to take a complete beginner through all the skills required to pass the exam. Personally l found this is the best explanation of how to query xml data using TSQL. They where for me worth the investment.
CBT Nuggets - an excellent resource, the videos l viewed for my 70-462 exam were really helpful. As part of the training package l signed up for included exam questions which l also found where excellent. More expensive that other sites, you have access to all the courses. Really worth considering if want to maximize your study time.
Udemy - with this provider you purchase one course at a time which allows you "lifetime access" to the course. When the courses are on sale, the prices are low. The quality of the courses can vary, so have a look a the reviews on the course before purchasing. Personally, l found the 70-463 covered the basics well, on the other hand, it did not go into sufficient detail for the exam questions.
Pluralsight - this is a well know training site (ok l have heard the name in quite a few place) there are lists of videos for specific certifications. The quaility of the courses l have watched were of a very high quality.
Microsoft Virtual Academy - its Free, which is not always a recommendation. That said the quality of the courses that l have viewed have been excellent. For the exams search for jump start videos, which are a really good jumping off point to start your studying. So excellent hints and tips in the videos from people who passed and training people for the exams.
SQL Bits - apart from being one of the best SQL conferences in Europe. The organizers have given back to the SQL community by recording some of the sessions and making the available for FREE on their website. To find what you are looking for might take a bit of searching, most topics will have at least one video on them.
YouTube - there are a lot of videos uploaded on a wide variety of topics. There will be some searching to find the topic you are looking. On the downside the quality of content is variable. Ranging from the excellent to the not so good.
Blog posts - again this will require some searching. It has in my experience, been worth the time and energy required. One author whilst studying for the 70-463 exam blogged about what she was learning as they went along.
Passing the exams
What is required to pass the exams? Practice and lots of it! One of the keys that are borne out by other people who passed the exams is the practice exams. These exam questions will not be exactly like the exam questions. What they will do is get in the way of thinking when doing the exams. Reading exam questions to see the question, examining multiple choice questions for the correct answer. Best way to get practice is to get hold of practice exam questions and take the exam. There are a number of providers which will allow you to purchase them, MeasureUp, CBTNuggets and others. They will not be exactly the same as the exam questions you take when you go into the exam room. On the other hand they will give you practice at answering the questions. If you pay attention to the score at the end of the practice exam you will also be able to see where you need to improve.
The questions are designed quite deliberately to test your knowledge, well you would not want them to be too easy ? This article from Pluralsight has some excellent examples of the format of the type of question you will be answering in the exam. If you are really interested in how the question are constructed and methodology behind them, this video from Pluralsight has an interview with someone who designs the exams.
All that remains to say if you have decided to study for a certification, good luck and happy studying.
I've been working with my current company for over two years now. During that time, on my own initiative, I decided to review the BI market to see what tool(s) that the company should be looking at adopting. There are quite a few restrictions, data privacy, our clients are very cautious about their data. So must be an on-premise server and yes I have asked lots of questions about this. Also, our clients are mostly non-profit or charities, the budget is a massive consideration.
PowerBI has been my tool of choice for reporting. It is used for a POC (proof of concept) project reporting service desk incidents to our clients. It is fantastic we had a Pro account, we shared the reports with our clients. We loved it, the clients loved it. During the next two years, I invested time, energy, effort, working on other POC projects. At the same time showing the relevant directors why we should look at Power BI for future development. The deal breaker was an on-prem server, no negotiation on that point. The start of 2017 exciting news, on-premise server was coming Which version would get it, how much would it cost, could we use it. Answers from Microsoft, zero, zilch, nada, nothing, brick wall impression.
So we waited and waited and waited. Then, Power BI premium. By the time I had digested the news, it felt like someone had kicked me black and blue. There is no point in even approaching our Managing Director with a minimum of £3k per month for this project. Our budget is not even in the same country, let alone same ballpark. Next, our sharing reports with other free accounts using Power BI Pro, at least for some of our client has gone. Now have it, now you don't.
Your company might be a large enterprise, then these costs are reasonable, we are not a large enterprise. So in essence over two years investment of my time down the drain, time to start again. Now I am in the process of contacting clients for the POC project to show them how to access the reports, as the can no longer use their own Power BI accounts. Disappointed would be a mild word to use to describe my feelings.
Very recently Tableau announced a price change. Long story short, my line manager saw the new pricing structure, complete with on-premise server, per user cost of $35 per month, PowerBI cannot compete with that deal. What will happen now is my company most likely to become a Tableau customer, Microsoft's loss. The tools released at the data summit (June 2017) now places PowerBI toe to toe with Tableau. Sadly I believe that Power BI will likely loose in the long run due to the pricing currently in place. Whilst I understand the business logic and reasoning pursuing this model. Microsoft has also demonstrated very clearly they do not understand the market in the way that Tableau seems to, which is reflected in their pricing structure. Great for me, another toolset to add to my CV. As I see it, Tableau leaves PowerBI dead in the water for customers like my company. There is NO competition, Tableau has this market to themselves. Which is bad news for me as a customer.
Microsoft has got it right before, yes I will stand up, shout, cheerlead, and applaud when they do get it right. As my tweet to James Phillps / Power BI team expressed. When Microsoft get it wrong I need to be just as vocal, and I believe they have got it wrong, with the pricing in a big way, at least from where I am standing. Yes I will continue to let people know about PowerBI, it not be with the same enthusiasm, that makes me sad :-(
Last but not least a more personal public apology to Chris Web (@Technitrain) was on the end of my rant via Twitter regarding pricing, sorry Chris, my bad.
We all communicate with each other, some more than most, in our house if my partner is not talking to me there is something wrong. It's not usual for the misunderstanding to be something I might have said (or done). Communicating with each other verbally is something that we learn to do from an early age. We learn what words mean, their power, what they can do for us, what to say, and what not to say.
There have been times I have had the pleasure of going to the local garage and speaking to the mechanic regarding whatever challenge is with the car. The mechanic would explain the issue to me, using words which I have to say that wished I understood. It is entirely possible that there is a "big end" in our car just do not ask me where it is or what it does. Or that the timing belt is very important to make the engine run properly.
Every industry, profession, hobby, has their own language. This often makes it easy for professionals to communicate with each other often in a form of shorthand which can sound foreign to someone else even if they speak the same language. Working in the IT industry this is something I am very aware of. If someone asks me what I do for a job what do I say? I might say that I am a BI professional, working primarily with the MS SQL server stack, sometimes using SSIS, and SSRS. I write a variety of CRUD scripts in TSQL and I do some query optimisation. If the person asking is not an IT professional who works in my specific area of expertise, most of my explanation would have sounded like I had spoken in a different language.
What I now say is my job involves three things, data shepherding, data grooming, and data dressage. I might expand a little on these to explain that l move data from one place to another ensuring none of the data gets lost as we move it. Some of the data might need to be polished or groomed to fit in its new home. Then I train data to perform and dance in a way that others can understand it better. What I try to do is use words which people who are do not work with databases can understand and relate to.
When I speak to clients one thing I try to remember is to use words that anyone can understand. If I introduce technical concepts or acronyms in the conversation I will try to make time to explain them. Or use analogies that are simple and easy to understand. This is not an easy thing to do, it is our job to make IT simple and easy to use. There might be lots of complicated moving parts behind the scenes. Like a car, we have a simple dashboard, underneath are lots of complicated moving parts that just work. The hard work of maintaining and fixing those parts I happily leave to the experts.
Personally, I see our job is to make our customers task as simple as it can be. We should present challenges, technical details in a language our customers can easily understand. As Einstein is quoted as saying “make everything as simple as possible but no simpler”. One excellent example of this is by Brent Ozar when explaining implications of RTO & RPO here -> https://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/. The worksheet sets out the terms in language everyone can understand, even better by drawing attention to the targets, so everyone knows what to expect.
Is this easy, or simple? No, it is not. Having said that which expert do you feel most comfortable with? would happily go back to time and again? The one that speaks to you in words and terms you can easily understand. Or one that uses language and words that are sometimes not easy to follow or understand? The choice for me is easy and simple, which is why I work that little bit hard to make it as easy as possible for my customers to understand me.