Background Think back to the year 2012 when Microsoft introduced us to SQL Server Analysis Services Tabular models. It was new and exciting. The Excel gurus who had been using PowerPivot since 2010 were sneering at us like we were…
This Week’s Topic: SSAS Tabular Ok so we missed a week. We had a busy training in Denver last week. Good news is we're baaaaaaaaaack and with a great lineup of Tabular Pros. For those of you not knowing what…
Guest Post by Andrew Todd
Intro by Avi: Andrew has been a part of PowerPivotPro family for a while now and has been doing wonders for the clients he has been working with. You have probably heard Rob talk about the “Data Gene”, well Andrew is the ideal specimen for that breed.
Recently I had worked with a client where we spent quite some time just trying to figure out how to connect Power Pivot to their Oracle database. Andrew here lays it all out in simple step by step approach. Andrew’s got a lot of wisdom to share and you should all look forward to more posts from him. Take it away Andrew…
The amount of data around us is staggering. Statistics citing the amount of data being generated daily describe it in terms of exabytes (1 exabyte is 1 BILLION gigabytes!) In fact, the amount of data generated daily now exceeds the entire storage capacity of the entire world in 1986!
In a world with that much data, we’re lucky to have tools like Power Pivot and Power Query that offer easy connection to data sources that just sort of … clicks! However, if you’ve tried connecting to an Oracle database in Power Pivot you might have had some trouble. Fear not, here’s how you can make your Oracle database connections click just like all the rest!
Why aren’t my Oracle connections ‘clicking’?
For Excel pros, Oracle databases are a bit different, because there are a few key connection enabling components that are not automatically included with most users’ setups:
1.) Connection details – for Oracle databases, the connection details are included either in the connection string itself (EZConnect) or in a flat file called TNSnames.ora. Don’t worry, it isn’t as complicated as it sounds!
2.) Oracle data providers – You can think of these as the communication link between Power Pivot and the Oracle database.
Make a Call to your DBA for some details!
Before you attempt to connect to your Oracle database, you’ll need to contact your friendly Oracle DBA for the following information:
Post by Rob Collie
***Update: check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.
***Update #2: a Free Version of Power Update is now available. More info here.
***Update #3: There is now a forum for Power Update questions, located here.
A brand-new software utility designed from the ground up as
a “Companion” to Power Pivot, Power Query, and the entire Power BI stack.
Definitely Click on the Image for Larger Version – Surprises Lurk Therein
Do Any of These Sound Familiar?
Power Update Helps With ALL of These (And a Few More, Too)
“What IS It?”
OK, a few things:
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.