SO reality hit yesterday, we needed to import the data from all a clients Active Directories – not many by any standards and all 2008 I still have nightmares about uncovering forgotten NT4 domains that are “business critical” – anyway you can use Power BI Desktop to easily import multiple Active Directories and then merge them into a table for reporting, that all works really well and is frankly a breeze. You say you want to use an Active Directory, give it some credentials and then select where and what you want to import. If you need to combine data then you just merge your queries.
So why are you wasting our time by making us read this?
It all falls apart when you start to set it up into an App ready to publish it. The Enterprise Gateway (On-Premise Gateway) doesn’t support active directory connections, so you have two options
- Hire someone to manually refresh reports all day – This is a great fun job and I’m sure your staff retention will be sky high
- Use a single Windows account for everything (that needs Windows accounts) including logging into Power BI and all your data sources – I can hear the panic in anyone with a security background reading this
Well neither of those options work for my company! So we chose not to choose naff, we chose life instead
Nope, if you came here looking for a fan site, I’m afraid this isn’t what we are, CSI or Continual Service Improvement is a process of structured change and development that ultimately leads to the improvement of a service being delivered.
The human brain sees “improvements” automatically, I’m sure we’ve all received a set of directions and having completed the journey a few times thought “I’ll bet if I just change… I would get to my destination quicker/safer/more economically”. That’s the simplest form of “Improvement” I can think of. The more analytical of you may have timed your journey different routes or used another benchmark (was it by a busy road, how much petrol did I use) the point would be you are actually making a comparison, the net result is that you are able to look at you journey times and make and informed decision on which route is the most appropriate. A few weeks/months later no doubt some more changes come to mind or open up or there are road works on your “optimal” route, does that mean having changed once that you can’t change again? Heck no, you’ll change your route to the best, who knows you many have even found that a different route was always preferable at key times/days. What you’re actually doing is living the Continual Service Improvement (CSI) process.
As you progress in Power BI (and Power Pivot), you quickly find you need a date dimension, the problem is that date dimensions are difficult to build and even more difficult if you want them to be more than just a list of dates.
“Stop I’m confused… how can a list of dates be more?”
The role of a good date dimension is indeed to be more than just a list of dates, there are functions in Power BI to produce a list of dates, the issue is that you want something that is more. I’ll be honest at this point before we dive into the queries, I use, I cannot remember where I got the original source, I found three or four different date dimension SQL queries initially and then started pulling them together and adjusting them to fit my needs. For me we were going to have the majority of our data coming from a SQL server so it makes sense to have a SQL date dimension, based on what we did and after doing some benchmarking within Power BI I would recommend housing it on a Database, putting it in Power BI or excel won’t stop things working, but I have got best results from this method.
The important thing about the first bit is that there are many, many scripts out there to do what you need. Typically they have the same format.
I’ll make these source files available to download
Populate Dates Dimension
- Deal with 1 character days and months
- Set Fiscal Year Months – where I work we use August to July so I needed to add a case statement here, case statements sound more complex than they are
- Set Fiscal Year
- Set Fiscal quarter
- Set Fiscal week