Tag: Power Query

Categories
Business Intelligence Technology

Microsoft Business Intelligence Now and Into The Future

10 years ago, it was SSIS/SSAS/SSRS

Then in 2007 SharePoint, PerformancePoint/SSRS

Then in 2010 Power Pivot in Excel/SharePoint, then Power View in SharePoint

Then in 2013 Power BI … Power Pivot, Power View, Power Query, Power Map.. In Excel and Office 365.

Now in 2015 Power BI Version 2. Not in Office 365, separate. Power BI Designer, or use the Power BI web site to set up your dashboards, mobile, etc.

All the while, the existing solutions that have been available previously are still there and available, making things… well, confusing to say the least.

Most shops .. It all depends on when they started going heavy BI with the Microsoft tools, on where they land. Also, how well they could move when things change, as well as how much they want to stay up to date with the tools.

If you started 10+ years ago, you probably have a good base of ETLs written in SSIS, as well as many multi-dimensional (MD) OLAP cubes in SSAS, and SSRS reports off your cubes and data warehouse, running in SSRS Native Mode. You started with SQL 2000 if you were lucky, with cubes and dts packages, but then SQL 2005, then 2008, 2008 R2, 2012, 2012 R2 and now are on 2014. You really liked 2005 SP2 and 2008 R2 for the BI features :). This setup is like the VB6 or .NET Winforms of BI. It will probably be around forever in some way shape or form but not a ton of updates and Microsoft has moved on.

If you started a little later you might have SSRS in SharePoint mode, and some Performance Point dashboards. You might have even used Performance Point for planning/budgeting (and loved it?) until Microsoft killed it. Then you had to look for alternatives for that, or use OLAP Cube Writeback. In my opinion, SSRS in SharePoint and Performance Point are dead. Not dead as in they don’t work or won’t be supported, but I see them as the wrong path, life supported direction. If you are still using these heavy I would look for alternatives.

Now it gets interesting. You started with Excel 2010 and PowerPivot (no space!) and had SharePoint 2010 setup and Power View in SharePoint. You created V1 Power Pivot models, they were limited, you could do some things, but still it was limited. You still needed to get data somewhere so SSIS ETL’s or something to get data in tables you can use. If you are using Power View in SharePoint, I would hurry up and look for alternatives, it is dead (my definition of dead like SSRS/PP in SharePoint). Excel 2010 is long past and V1 PowerPivot is dead too. Seems like this era was short lived and just a stepping stone.

Then, in 2013, Power BI. So they added a space to Power Pivot 🙂 .. And made it better, v2. Added missing features, Tabular SSAS cubes even! And Power View could be used in Excel. They both came by default in Excel (depends on version) but turned off. Power Query came out of nowhere and is awesome and Power Map, while buggy, was better than nothing. But what do you do with all these solutions you build? Where to publish? Not SharePoint on prem? But Power BI in SharePoint Online.. So you need Office 365 and Power BI subscription. You set up Data Management Gateway so you can get to your on prem data sources. You can refresh once a day or manually. You can do some pretty cool things, create workbooks with pivots and Power Views.

But you are missing things. Missing things like the ability to schedule a report to run and email someone, like SSRS. You are missing awesome formatting abilities for every pixel like SSRS. You wonder when SSRS is going to come to Power BI or what your options are… you hope you see iterations and features released to Power BI as that is the path, but then..

New Power BI Preview comes out in 2015. It has a standalone Power BI Designer (reminiscent of the Performance Point designer) that lets you create reports, dashboards and save a file to publish to the NEW Power BI portal. So you have two Power BI portals.. New and old. They don’t overlap or talk to each other, the licensing is different, etc. The old Power BI lets you connect to SQL on prem with refresh with the DMG and other data sources, etc. The new one does not. The new one lets you connect to GitHub and SalesForce and Marketo, but not other data sources that the old Power BI did. The new Power BI lets you connect to on-prem TABULAR SSAS cubes with refresh, but not MD ones (yet). The new Power BI lets you connect to excel data in OneDrive/OneDrive for Business. So could one publish a data file out to ODFB to faux refresh? I have yet to try. The new Power BI lets you publish dashboards to the iOS Mobile apps and also  embed (up to 10 MB – which needs change to be bigger) on websites. New Power BI has an API that lets you create your own connectors / REST API for things. And the list goes on and on.

So where does that leave us? Well, of you invested time and money in BI the last 10 years, you might feel like Microsoft is abandoning you. It kind of seems that way. You need to change or get left behind. But what do you change to? Change your MD cubes to Tabular? Rethink your architecture? Sync data to Azure?  Power Pivot/Power Query? Abandon SharePoint as a BI tool? Move your reports from SSRS to something else or Power BI (if you can?) I am unsure. Still trying to figure it all out.

One thing for sure is, it will always keep evolving. Me, I would say, tabular first if you are on prem. Try to use Power BI where you can. Minimize SSRS reports. Use SSRS native instead of SharePoint. Stop using PerformancePoint if you are still using it or thinking about it. I bet at some point SSRS comes to the new Power BI – there is an item on the UserVoice forum already asking for it. Try the Power BI Designer and Website and see what you can do. Always be trying to get something going in the newest and latest technology/tools available.

Have Fun with Microsoft BI now and what is yet to come!

 

Categories
Business Intelligence Technology

Using Power Query to Analyze Your Schedule

I am in a lot of meetings. A LOT of meetings. Double, triple, quad booked. I guess when you get to manager or director level somewhere, that is the definition of “busy”, or maybe everyone just wants you in their meeting, or your opinion, or whatever. In the end “Meetings are Toxic” (from 37signals), but really the are sometimes a necessary evil.

Anyways, do you really know where you spend all your time? Well you can glean the information pretty easily using Excel and Microsoft Power BI (Power Query specifically).

First, the key for me is to “categorize” my meetings. You can create categories in Outlook and then assign them to meetings, you can even color code the categories.

Where does Power Query fit in? Well, you can connect to Exchange as a data source.

power query exchange

Then you can query your calendar “table”, and pull it into Excel.

power query navigator

power query

Then, as with any table, you can Pivot it, and pull over category as the row, and look at the count. With some column work in the Power Query query, you can split out the date/time and get Month/Day/Year and create a semi-hierarchy, to see things over time.

MeetingMonth

For example, I took over 2 teams in January, and my meetings with them and related projects skyrocketed in January. Now I know what was taking my time up for Q1 2014 🙂

meetings over timeAt least the number is going down 🙂

There is so much more you can do with Power BI and Exchange data, your email, calendars, contacts, etc, this is just the tip of the iceberg, and it should only take you 10 minutes or so to get to this result! Now, if I can just figure out how to get out of the meetings!

Categories
Business Intelligence Technology

How Cold Is It?

With the latest “Polar Vortex” or whatever that is happening, EVERYONE is talking about the weather. Everyone always talks about how it has been this cold many times, etc, etc. “It was colder in my day” – ok. Well prove it!

So I took a look at the NOAA data you can get here http://www.ncdc.noaa.gov/cdo-web/ and got an extract to CSV for my hometown of Chisholm, MN (actually the Hibbing/Chisholm airport since it has data from 1962 to today)

I downloaded the CSV, opened in Excel 2013 and imported into Power Query. I think did some formatting to get the date parts and a date field, and converted the “tenths of a degree of Celsius” to Fahrenheit. Then started analyzing.

I will have to refresh this after this cold spell, because it only has data to 1/1/2014 and these last few days have been cold, but, not the coldest.

Back when I was 16, in 1996, there was a stretch of days in January that were COLD. The data supports this. First I took all the days with a Low temp of UNDER -35 degrees F.

Chisholm Low Temps

 

You can see, there are a bunch of days in Jan/Feb 1996 that were UNDER -35 Degrees F. So then I copied that pivot and expanded on that date range to see all the days.

JanFebChisholmLowTemps

 

Pretty dang cold from 1/19/1996 to 2/4/1996. Lowest day was -50 Degrees F. Average of -31 Degrees F. Of course these are “real” temps, it was even colder with wind chill. These last 3-4 days of -20 to -40 are cold, but not sure they are colder than in Jan 1996. We will see when it is all said and done.

If you can’t remember how cold it was, NOAA, Excel and Power Query can remind you. 🙂

I have the spreadsheet up on Skydrive. http://sdrv.ms/1gBwMPL

Categories
Geeky/Programming

Analyzing ADFS IIS Logs

If you are using Active Directory Federation and you want to see what users are logging in when to what external service, you can analyze the ADFS server IIS logs. It is pretty straightforward since it is just IIS.

First, get to your ADFS box, get to the IIS log directory, usually something like “C:WindowsSystem32LogFilesW3SVC1” and grab those logs.

Install LogParser on your machine.

Now, you can write sql type queries against your logs. For ADFS logs, we don’t care so much about many of the columns, but primarily username and date, maybe the URI for filtering, maybe the referrer or the user agent to see what browsers your users are using, but to get say, unique logins per day for a given service, we just need the date, username and URI.

Remember the date is probably UTC so you need to use a function to convert, or leave as is if you want, and everything is pretty much all relative depending on how accurate you want things to be. hint: TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp

Now, here is the LogParser query:

logparser "SELECT DISTINCT cs-username, date INTO FROM WHERE cs-username NULL and cs-uri-query LIKE '%your service%'"

Note in the statement the output path and your log path, change to what yours are. Also, the LIKE statement. For example, to query for Microsoft Dynamics CRM Online, I used

LIKE ‘%dynamicscrm%’

Run that query, then open the .csv you exported to. Format the data as a table, pivot it by user, pivot by date. Get the unique number of days using a date diff, analyze logins per day, logins per user. Tie to Active Directory (using Power Query) to add some dimension attributes like title or department and very quickly you can analyze what users, departments etc are using your service.