Direct Query: Power BI on drugs… probably sedatives

Direct Query: Power BI on drugs… probably sedatives

It’s no secret that I think Power BI is great, even on days like today when Power BI has well and truly shafted me. Half the day has been lost by crashes and everything is getting tense, but still Power BI is going to deliver. The biggest challenge you’ll face with Direct Query is that it is only able to do a subset the things that your normal “full fat” Power BI deployment can do. As you no doubt read in my other post “A Plan Forms” lots of businesses will like Direct Query, certainly as a day one proposal when starting in Power BI.

Once you start down the Direct Query route be prepared to consider that you have to make sure there’s plenty of headaches on the road to victory. Undertaking it though is well worth it. I will continue to keep you informed of my progress and you can sneer at my mistake and hopefully we can have some thigh slapping moments together.

 

Upwards and Onward my friend

So you need to do a comparison across time periods, this has always slightly confused me I’ll be honest. Earlier though I had to figure it out, basically the requirement was to work out what happen in the last month and in the last three months. The idea being to understand if things are improving over time or (please don’t be the case) getting worse.

Now with all the time intelligence that’s out there in Power BI it is really easy to do much of this and DATESINPERIOD() is a real friend. I will no doubt end up adding another tag for Time Intelligence formulae or methods because they really are the ultimate glue that holds most of Power BI or PowerPivot together. Let’s face it half of the time you want to compare things over time don’t you?

DATESINPERIOD(<Possible Date Values (column of dates)>,<Context Date for reference>,<periods for range>, <Period selection>

with Time intelligence it is really important to make sure that you have a dates table. Recent advances in Power BI mean your date table can be much simpler, but you still need to think about what exists in that table and what features are a must from there.

The context date is really the pivot around which Dates in period works I don’t want to call it a start date because you can go forward in time from there or backwards it is the starting point, but I find that simplistic.

Periods are the key here, firstly how many if that number is positive then you will count on that many periods from the context date if it is negative you will be counting backwards from the context date. Possible periods are Day, Month, Quarter, Year

So let us see a sample

=Calculate([Measure],DATESINPERIOD(Dim_Calendar[Date],[SpecificDate],-3,MONTH)

So break this down…

  1. Calculate – Basically work this all out and a return a single (scalar) value
  2. [Measure] – What are you actually wanting to do, you could call an existing measure or write one here
  3. DATESINPERIOD(Dim_Calendar[Date] – return a list of dates from the column
  4. [SpecificDate] – Context value a date that will be used to reference the periods against
  5. -3, MONTH – Month refers to the period and a count of those in this case -3 so go back three month

The clever bit for me was to get the [SpecificDate]

=ENDOFMONTH(DATEADD(Dim_Calendar[Date],-1,MONTH)

What was that?

Break it down, basically it returns the last day of the previous month (with data), typically you run this against a transaction table to try and get the correct context, but I’m sure you could run it against your Dim_calendar table, I just did it differently in this example what are we doing

  • Find the single value (scalar) for the last date in the column
  • Filter the column to be a month ago

The worst possible thing about time intelligence is it mostly isn’t compatible with Direct Query.

Our new home

Our new home

So we got a new site and with it a new home, now all of a sudden I’m doing things properly, I have a proper website with a grown up URL and everything. I hope you enjoy your visit and drop me a line.

So after six months or so of sitting about and not really moving forward I took the advice of a good friend and pulled the trigger. We’re now here at www.bi4csi.com. I hope the change isn’t too much for any of you. Feel free to message me and send virtual house warming gifts

“Hello World”

A plan forms

A plan forms

Like many companies the company I work for has concerns about the cloud, like most we’re certain that if any of our data ever touches the cloud then childen will be sacrificed and hostile states will take ownership of all our secrets. Maybe there’s some truth in that or maybe they’ve been watching too much “Mr Robot” wait does that mean I work for Evil Corp?

I’m sure the truth is somewhere between the two extremes no matter. The argument is a common one and it is one that really Microsoft entered the market place I believe unprepared to really deal with – sure the late data aquesition of DataZen kind of went some way to showing there was an intent at on-premise and of course Piramid (still never actually seen this up and running or working, could it be a myth (am I talking to you? Do you understand me or is this just a lame attempt at 4th wall interaction). Mind you Microsoft’s own book kind of spells things out the best I’ve seen so far. My personal version is that SSRS was losing ground to other ERP solutions SAP and Oracle really pushing thier own solutions, SQL based Data Warehouses were starting to really suffer, they’re cumbersome and the cost of ownership has increased as SQL licence requiremenrs have increased,  six figure sums just for SQL licences for solutions make them costly and the benefits are hard to realise. Then everything changed… my guess is around 2010 challengers started to come up. Suddenly it was possible to do things without the established vendors I remember in 2007 writing an Access Database that services circa 10’000 users. I’m not going to claim it was particularly good or that it was massively transactional, but when compared to the traditional view at the time of “buy a SQL licence” was just wrong. Some work and some thought and it was invalidated. That was just my personal experience. Looking at the market out there, solutions becan to use SQLite or MySQL or whatever the customer was running, mean while the ERP giants of Oracle and SAP continued to expand the application usage within their customers again as people consolidated their applications and looked to maximmise their investment.

2012 (or there about) cloud began to pick up, yes it still took another couple of years but the cloud solutions started to come up in 2012, Enterprise level organisations started to discuss private cloud and what that would mean for them. Into this environment and at this time Microsoft decides to reinvent it’s BI strategy, but quietly (SQL Enterprise licences are still a great seller, why risk that too soon and of course risk your business until you’re sure). So quietly Microsoft launched what I always think of as the “Power Suite” for Excel, PowerPivot and PowerMap initially, then PowerQuery and Power View were added. The purpose of this was as PowerPivot.com will tell you, a new way of dealing with data was needed.

Wowsers Ross stop prattling on …is that another 4th wall interaction?

Ok so where are we? Yes the paranoia of a business these days is around putting data in the cloud so PLAN A: is to not use the cloud storage – “Eat my shorts Dark Army” – our data will remain in our data centres BUT all queries will have to travel across the WAN. PLAN B is to use Microsoft Cloud Storage, but having the end customers demand the cloud storage is better than me suggesting it.

The first draft of my 3rd Data Model is already in play and goinART3.0g through testing, so far it’s not too bad. Direct Query to SQL seems to be ok with Power BI Desktop – just awaiting our clearence for Power BI Pro to do true gateway testing via the cloud. So far our Incident Managment, Request Fulfilment and Satisfaction Surveys are able to be reported against. Make no mistake using Direct Query requires much more planning, but the rewards are there. I’ll keep posting more as I get further into the whole Direct Query rabbit hole!

Keep on developing folks!