Resuming from Last Week Last week’s post left off here: most BI still relies on the storage layer (SQL or its many cousins) to be the brain, and storage layers are terrible brains because they were designed for storage, not…
Intro from Rob There are few things better than a guest post from Tom LaRock, and that's what we have today. Few people know SQL Server - and databases, for that matter - as well as Tom. He was President…
Hello P3 World! My name is Krissy Dyess and I have had the most amazing experience working as a full-time consultant at Power Pivot Pro since April 17, 2017! Today is 1/9/2018 so that would be… 266 calendar days, if…
Welcome to the latest PowerPivotPro Coffee Talk, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel, and are then lightly…
by Matt Allington I have learnt a lot over the last year working full time as a Power Pivot professional. And some of the things that I dismissed as “not useful” very early on have turned out to be very…
Guest post by Thomas Allan
Intro by Avi: As in Excel, in Power Pivot there are often many ways to accomplish the same thing. For example you can source a Date/Calendar table from Excel, Azure (here and here), Power Query, and SQL (this post). That is usually a sign of strength of the tool. Although it also makes it more challenging/fun to be able to weigh the options and decide which one works best for you given your situation. Thomas, shows us a cool way to pull Date table from SQL.
Stepping back a bit, there is some good interplay between Power Pivot and SQL. In terms of feeding Power Pivot using SQL – see Why PowerPivot is Better Fed From a Database Part 1 and Part 2. And also Power Pivot being a great addition for SQL savvy folks – see I Know SQL Queries, So Why Do I Need Power Pivot?. Goodness all around, I say 🙂
Take it away Thomas…
In addition to often mentioned benefits of using SQL servers as data stores (flexibility, reliability, scalability and security), the benefit of linking to a centralized source that delivers results quickly, consistently to practically any client, anywhere, adds a powerful dimension of “portability“ from the outside of the Excel workbook as Power Pivot and DAX formulas offer on the inside.
Four types of resources are often used to create date tables within Power Pivot: 1) Excel itself, using formulas or VBA, 2) data feeds, which you can find an example of following this [link removed due to 404] hyperlink, 3) Power Query, which you can find an example of following this hyperlink, and 4) relational databases.
The example that follows was developed using the relational database SQL Server 2012 and uses only table-valued functions. Although the code was developed on SQL Server 2012, it was also tested on a 2008 release of Microsoft’s flagship database product.
For demonstration purposes, the solution offered here is based on a calendar fiscal year (quarters start January 1, April 1, July 1 and October 1). For other types of calendars, such as 4-4-5 or school semesters, the code can modified by a SQL developer (also, for other types of calendars, some architectural issues may also apply inside Power Pivot, which are fully explained in Rob Collie’s comprehensive Power Pivot course).
This post assumes that the reader has basic familiarity with SQL Server Management Studio, sufficient to install table-valued functions, or has access to someone who knows how to install table-valued functions. This post also assumes familiarity with connecting to a SQL Server database from within Power Pivot (similar to connecting to an Access database).
Download SQL code below.
Post by Rob Collie
Got This Question the Other Day, and it is LONG Overdue That I Answer It
Some of You Are Confused, Some Are Nodding
Generally speaking, I think the people reading this fall into one of a few camps:
- People who are early in their Power Pivot journeys, and who also do NOT know SQL (most Excel Pros fall into this camp at some point, before hopefully moving into group #2 below).
- People who are pretty good at Power Pivot, but do NOT know SQL (I fall into this group).
- People who are good at BOTH Power Pivot and SQL (this is a blessed group).
- People who are good at SQL but still early in their Power Pivot awareness/knowledge.
Group #4 is the “target audience” for today’s post, but it’s still relevant for groups 1-3, because we WILL get asked this same question from time to time, and it’s good for us to be able to answer.
“I Started Out as a DBA…”
For Many People and Organizations, THIS is Business Intelligence
(And to a Certain Extent, This is Effective, So it Persists as a Workflow)
Let’s say you began life a a DBA. Which means you know SQL, of course, but writing SQL is not the only thing you do as a DBA. You’re maintaining indexes, watching for bottlenecks, talking about I/O, number of spindles, TempDB… all that good DBA stuff that I understand at a conceptual level but have never learned to actually DO.
But one day, someone from the Business has a question. They figure all the data required to answer it is “owned” by you, so they come to you with said question.
And hey, it turns out that you CAN write some SQL and answer the question! Which is pretty damn helpful and makes everyone involved feel pretty good. (Hey, we are all still fundamentally wired for cooperation after all). It also makes you more relevant to the front-line business, and no longer “just a cost center” from the perspective of the company’s leaders, which is VERY good for your career.
Guest Post by David Churchward with Technical Input from Scott Senkeresty The first thing I ever do when I create a new PowerPivot workbook is create the date tables that I need. Until now, I’ve used a preconditioned SQL table…
Guest post by David Churchward If you’re anything like me, you will have eagerly sucked data into PowerPivot, keen to get cracking with solving all of your reporting needs. If you’re an Accountant (amongst others) doing this, you will…