Excel is one of the world’s most popular BI tools.
It’s also the source of much unhappiness. (See, for example, my post The Pivot Table Gateway Drug.)
So, why do I think suffering in Excel can be the first step to BI success?
Because if your users are doing something month after month in Excel — and it’s consuming hours and days of their lives — you can immediately deduce a few things:
Read more…I recently presented “NAV SQL from Friends to Foes” at the recent NAV User Group Summit.
Here’s a copy of the presentation:
Here’s the code I reference (in GitHuB).
My presentation is a whirlwind tour of the NAV database and SQL. But still, it’s a good starting point for folks who want to get comfortable with them both.
As I continue to build on this topic, I’m going to write about it here.
And I’m going to start by explaining how NAV is (and isn’t) a relational database.
And the first related topic is duplication of data — because this is a common source of confusion that all begins with NAV reports.
Read more…In this article, I’m going to delve into the topics of MAXDOP and cost threshold for parallelism.
I’m writing it for two reasons:
1. There was a recent thread on SQL Server settings on the NAVUG list. Some of the information was good. But much of it was expressed in terms of “Well, I heard…,” which makes me nervous. You should have a basic understanding of what various settings do before you start taking anyone’s advice. (This isn’t to make me look superior. I’ve had plenty of times in my life where I (or my team) have been caught in the “let’s give this setting a try” loop — and it’s not pretty.)
2. I’m preparing a presentation on SQL performance for NAV. While I’ll be focusing mostly on improving BI query performance (which is where I spend a lot of my time), I do get asked about server settings, so I think it’s a worthwhile topic.
Read more…Almost every one of my clients depends on some kind of SaaS solution for some part of their operation.
Whether it’s payroll, accounting or electronic records, the data is controlled by their vendor of choice.
This is all well and good for basic processing. But too often, it doesn’t work well when it comes time to actually understand the data or (even more so) combine the data with data from other systems.
Why? Because if my clients weren’t careful when they chose a solution, they’re probably now reliant on the SaaS reporting tool provided by the application if they need to create any custom queries or reports. Or worse, they have to rely on the reports the vendor provides.
And this leads to problems.
Read more…A client recently asked me: “When I run a report, how can I know which server/company I’m using?”
It’s a good question — and one that others may have.
Below, I’ll walk you through the answer.
And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.
Read more…Last fall, my attendance at the SQL Summit was entirely justified by a presentation by Aaron Nelson, a SQL Server MVP on Excel, PowerShell and SQL Server.
Unfortunately, I regularly suffer from having Excel as a data source. In an ideal world, this wouldn’t happen.
Logically, no one would buy a SaaS solution without first figuring out how the data in that system would be accessed and combined with all the other data your organization needs.
Unfortunately, things aren’t always logical.
I was talking to a friend/prospective client recently about the accounting knowledge that is a key part of my work.
We agreed that in too many non-profits, the board’s finance committee doesn’t perform its necessary function. The committee members look at a few reports when they meet, but they don’t really dig in. They may have a soft connection to “numbers,” but they aren’t always great at picking apart financial statements.
So, the question came up: if you want to evaluate financial risk in your non-profit, where would you start?
When an organization has data questions, those questions can sometimes be answered by writing reports directly over the systems where the data was entered—whether that’s an EHR (Electronic Health Records) or accounting system. Often, this approach works just fine for financial statements and basic operational reporting. (Indeed, I’ve written before (back in 2011) about how to use the general ledger as a simple datamart/data warehouse. And I’ve run many projects using this method.)
However, this approach breaks down as data becomes more complex, such as when you need data from multiple systems to answer your questions. Unfortunately for my social service agency clients, this is almost always the case.
Many, if not most, of the SSRS reports I write end up being exported to Excel. But unfortunately, many reports don’t export cleanly. More often than not, you wind up with merged cells and blank spaces you don’t want.
In this blog post, I’ll show you how to build your report correctly for a cleaner export to Excel.
If you’re reading this post, you want to be more data driven. How do I know this?
As I shift my business to social service agencies, I’ve been talking to many executives in those agencies. And almost every one of them tells me they want to be more data driven.
So, I’m guessing that—if you’re like most of your peers—you want to be more data driven too.
But the question remains—where do you start?
If you want to be more data driven, you need to start by finding a good problem to solve. But what makes a good problem?
