Refresh This !

Today is April 3, 2014. I hope that if you are reading this on June 4, 2014 you will be able to disregard the following – but for today, and for me, it is true: refreshing the data in a Power BI mounted document from an on premises SQL server is a hellacious experience.

After several days of frustration, things are suddenly looking much better.

Posted in Uncategorized | Leave a comment

Navigating Sharepoint 365 Administration – Creating Power BI Libraries

If you have spent any time watching SharePoint videos on YouTube or elsewhere you may have noticed that there are many that demonstrate the rich feature set of SharePoint and few that actually show you how to get anything done. This is too bad because sometimes I think SharePoint administration is more difficult than creating PowerPivot data models.

Bucking this unfortunate trend is Melissa Coates of SQLChick who has produced a video about creating Power BI report libraries in SharePoint 365. Admittedly, one doesn’t usually need to create libraries until he has already created many reports, but watching Melissa administer SharePoint while giving a clear explanation of what she is doing has been very helpful in advancing my overall understanding of the system. The video can be found here: . I recommend watching even if, especially if, you don’t know why you might want to use report libraries at all.

Posted in Uncategorized | Tagged , | Leave a comment

Of Stars and Snowflakes

Pardon me while I work thru some very basic issues of data model design. What I’m dealing with is a basic contradiction. On the one had, Rob Collie suggests that I should keep my columns in my primary data table to a minimum. But, cube design basics suggest that I should lean more toward the star model than the snowflake. I can’t have it both ways.

Here is a snowflake which requires only one reference column in my primary data table:


And here is the star which requires three reference columns in my primary data table:


So which is better? I’m gonna go with the star here, in spite of the additional reference columns. Why? Because a star, pretty much by definition, means using more reference columns so when they say “use a star” they must necessarily mean “go ahead and use more reference columns, it’s worth it.”

Posted in Uncategorized | Leave a comment

The Struggle

Continuing with my dive into Power BI for Office 365. Overall it has been a very positive experience. and the source of some renewed energy in my career – and that is something. But there are some puzzles that I haven’t been able to get worked out yet.

First, there is the trio of Powerpivot, Power View, and Power Query. I’ve been able to get the gist of what each’s function is but it still doesn’t make total sense. Frankly, practically everything that needs to be done can bed done in Powerpivot, right? Power View and Power Query each serve as substitutes for subsets of Powerpivot functionality, but neither does so convincingly. The most egregious example of this is that, while Power Query is nice to work with and provides access to many public data sources, the fact that any Power BI document built with Power Query can’t be automatically updated from my office SQL Servers is an absolute deal breaker. I honestly can’t believe they would release a product with that deficiency.

Power View is in a similar state though not so extreme. There is no question that Power View produces lovely reports. Hell, I used Power View reports to sell my boss on making the Power BI commitment. They have their purposes. But at the end of the day, there’s too much missing. The lack of Top 10s and, especially, timelines make it far less useful. That and the fact of just having two different presentation systems adds another layer of complexity that seems pointless to the beginner.

Finally, there is SharePoint. I’ll grant you that SharePoint is an ambitious system. It does a lot, it’s very powerful, and it’s very confusing. If you ask me, and no one has, Power BI should have it’s own system for report sharing. I can see how incorporating Power BI into SharePoint makes sense to some, but it doesn’t for me. It’s a mess as far as I’m concerned.

Finally again, the lack of support for compound keys in linking tables makes life way more complicated than it needs to be. I hope they will fix that soon.

Anyway, I will persevere. Sorry to have gone on so long. I’ll have better news to report soon. I’m sure of it.

Posted in Uncategorized | 4 Comments

A Basic Data Model

I work in Retail which involves a lot of small transactions.  Importing sales line item detail into my 32 bit system for anything more than about one year of sales just doesn’t work. I’ve gotten around this by modifying my “select” statement within Power Query to calculate sales for each item per store, per month. This allows me to comfortably import about 4 years of data which is much more useful.  With this summary data I can still create about 90% of the reports that I need.

The funny thing is, I always imagined doing something like this in SQL Server but never got around to it.  With Power Query it was easy.

Posted in Uncategorized | Leave a comment

Presenting Large Tables in Power View (Can I, not How To)

I am new to Microsoft BI. I’ve been working with Crystal Reports for about 10 years and am ready to expand my horizons with this new technology.  The thing is, my users are accustomed to working with tabular data.  A lot of my users are product buyers and they like to look at numbers related to lots of products all at one time.  This involves tables, often quite large tables.

What I am finding, and I hope I am wrong, is that Power View is not at all oriented towards working with tables.  So far I can’t find a way to change the size of a view or even scroll laterally when there are simply too many columns to comfortably fit within a view.

Is Power View only for dashboards? Am I missing something? Am I just using the wrong tool? I would love to present my buyers with interactive, screen based reports for inventory and sales analysis, but maybe this is the wrong system for that.

Posted in Uncategorized | Leave a comment