Coffee Talk

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 Tabular is (but somehow brave enough to click on this blog post), Tabular is “Big” Power Pivot also what Rob calls the “apex predator of the MS BI stack.” It sounds intimidating but if you know how to do Power Pivot or Power BI, you also know how to do Tabular – it’s all the same stuff “under the hood”. Very excited to put this coffee talk out there featuring four people I have an enormous amount of respect for, both in their technical chops but also in the generosity, humor, and all around A+ personalities. Enjoy, and share!

Welcome to an experimental new feature here at PowerPivotPro, 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 edited for publishing on Friday. In our 3rd installment of our Coffee Talk series we’ll be chatting with three Tabular Pros. Let’s introduce the folks we brought in for this week’s Coffee Talk:

Chris Webb, a UK-based consultant and trainer specializing in Analysis Services, MDX, Power Pivot, DAX, Power Query, M and Power BI. He blogs about Microsoft BI at https://blog.crossjoin.co.uk/ and is also the author and co-author of several books including “Power Query for Power BI and Excel”, “Expert Cube Development for SSAS Multidimensional Models” and “SQL Server 2012: The BISM Tabular Model”. He can be contacted via http://www.crossjoin.co.uk/ 

Javier Guillen, is a Principal at BlueGranite, an analytics consulting company. He has expertise in SQL Server Analysis Services, MDX/DAX, Power Pivot, Power BI and data governance.   He is an adjunct professor in the Data Science program at City University of New York (CUNY) and the co-founder of the Charlotte’s Business Intelligence User Group.

Bill Antonis a beaver. Unlike traditional beavers, who waste their time in the woods splashing around in streams with sticks, Bill uses his time efficiently to construct data warehouses and business intelligence solutions using the Microsoft stack. His natural habitats include North American office parks where he is often surrounded by pleased clients. Bill specializes in the design, development, and performance tuning of Analysis Services. He’s also an avid contributor to the SQL Server community, speaker, blogger, and published author.

Ryan Sullivanis a lead consultant here at PowerPivotPro.  He’s jumped out of an airplane more times than you’ve ridden on an airplane. Famously here at P3 Ryan proclaimed on his first day of work “I can’t believe people are paying me to do this.” 9 months later we’re still paying him to transform organizations’ analytics programs, because he is really, really good at it. (Thanks Ryan for stepping in this week and covering for me –Austin)

Introductions

ryan
2017-07-19 15:18
has joined #coffeetalk_tabularpro

 

bill.anton
2017-07-19 20:08
has joined #coffeetalk_tabularpro

 

javiguillen
2017-07-19 23:18
has joined #coffeetalk_tabularpro

 

chrisw388
2017-07-20 11:51
has joined #coffeetalk_tabularpro

 

ryan
2017-07-20 15:59
Hey All!

 

javiguillen
2017-07-20 15:59
hi all

 

chrisw388
2017-07-20 15:59
Hi

 

ryan
2017-07-20 16:00
@bill.anton nope everything will be here!

 

ryan
2017-07-20 16:00
My name is Ryan Sullivan and I am a consultant here at PowerPivotPro. I?m joined today by three SSAS Tabular experts, Bill Anton, Chris Webb, and Javier Guillen.

 

javiguillen
2017-07-20 16:00
bill you look good for a casual Thursday

 

ryan
2017-07-20 16:00
:joy:

 

bill.anton
2017-07-20 16:00
oh crap, <disabling camera>

 

javiguillen
2017-07-20 16:01
:slightly_smiling_face:

 

chrisw388
2017-07-20 16:01
I hope you’re not doing this on billable time!

 

ryan
2017-07-20 16:01
So, welcome guys! Thanks for joining

 

javiguillen
2017-07-20 16:01
lunch time here in Charlotte. Thanks Ryan!

 

chrisw388
2017-07-20 16:01
It’s a pleasure to be here

 

bill.anton
2017-07-20 16:01
thanks for having us

Introductions

 

ryan
2017-07-20 16:02
How about some intros before we dive in?

 

javiguillen
2017-07-20 16:02
good to see you Bill and Chris too!

 

chrisw388
2017-07-20 16:02
Hi, I’m Chris Webb, I’m a Power BI and SSAS consultant/trainer based in the UK. I blog about both of these topics at https://blog.crossjoin.co.uk/.

 

javiguillen
2017-07-20 16:03
Hello, I’m Javier Guillen, I am a principal consultant for BlueGranite – an analytics (and advanced analytics) consultancy based in Michigan, USA. I work on topics ranging from Power Pivot, to MDX/DAX/ to Tabular, to Data Governance.

 

bill.anton
2017-07-20 16:04
Hello, I’m Bill Anton… I run an Analysis Services / Power BI consultancy (http://www.opifexsolutions.com) out of Atlanta, GA? most of our work is related to architecture, complex modeling scenarios, and a good deal of performance tuning. I also work closely w/ the Azure Analysis Services product team and have several clients currently running SSAS 2017 Tabular!

 

ryan
2017-07-20 16:04
Great!

 

bill.anton
2017-07-20 16:04
learned most of what I know about SSAS MDX from Chris… 😉

Tabular, Remind Me Again What That Is

ryan
2017-07-20 16:05
While some of our readers might be more familiar than they think with SSAS Tabular, they might not know it by name, or what’s special about it

 

ryan
2017-07-20 16:05
So what is SSAS? and what is Tabular?

 

bill.anton
2017-07-20 16:05
Power BI’s big brother?

 

chrisw388
2017-07-20 16:05
One engine, many faces!

 

javiguillen
2017-07-20 16:07
that is what I always say too. SQL Server Analysis Services – Microsoft ‘semantic layer’ powering analytical reporting. I allows for fast, easy querying of data on an ad hoc basis (in other words, drag and drop slicing and dicing – no need for SQL knowledge)

 

ryan
2017-07-20 16:07
Yea, so there’s this connection between PowerPivot, PowerBI, and SSAS Tabular. They’re the same engine!

 

ryan
2017-07-20 16:07
So what makes SSAS Tabular the big brother?

 

chrisw388
2017-07-20 16:07
Yes, although SSAS comes in two flavours: Tabular and (the older) Multidimensional.

 

bill.anton
2017-07-20 16:08
better suited (currently) for larger datasets…because of partitions… and several other featuers…but partitons is the big one…

 

chrisw388
2017-07-20 16:08
There are several other engine-level tweaks that make Tabular better suited for handling large data volumes too

 

bill.anton
2017-07-20 16:08
the lines are starting to blurr with the pending SSAS 2017 release

 

javiguillen
2017-07-20 16:08
and given its the same engine (with the same technical foundation across the board), it is not only a technical achievement but it has potential to impact the spectrum of reporting (from self service/business driven BI to corporate/I.T. driven BI) as all participants operate under similar modeling rules and using the same language (DAX/M)

 

ryan
2017-07-20 16:09
Javi, that’s a great point! These tools have a way of getting the biz and IT on the same page

 

bill.anton
2017-07-20 16:09
ewww, that’s Javi’s sweet spot talking point!

Blurred Lines, Biz & IT

chrisw388
2017-07-20 16:09
I think the way MS have started blurring the lines between when you would use SSAS Tabular (on-prem or in the cloud) and Power BI is one of the most exciting parts of the MS BI story

 

bill.anton
2017-07-20 16:10
agreed

 

bill.anton
2017-07-20 16:10
(didn’t always feel that way, but starting to see the light)

 

ryan
2017-07-20 16:10
OK, so it’s confirmed. The BI nerd level is off the charts for all 4 of us

 

bill.anton
2017-07-20 16:10
haha

 

javiguillen
2017-07-20 16:10
definitely. These tools provide a foundation for collaboration – although the purely process angle (the human side) must be defined by somebody — in other words, the tools (like super hero powers) can be used for good or for bad :slightly_smiling_face: is up to people to decide how if they want to promote collaboration or not.

 

chrisw388
2017-07-20 16:11
Though from the point of the view of the BI pro, it can be a bit disconcerting that the old dividing lines are disappearing – which is I guess why you felt uncomfortable too

 

ryan
2017-07-20 16:11
I’ve worked with a lot of organizations that can identify with those growing pains

 

bill.anton
2017-07-20 16:12
@chrisw388 … yes – precisely… the role of the Tabular-pro is shifting

 

chrisw388
2017-07-20 16:12
I think the fact that all of us here come from a traditional corporate BI background and yet describe ourselves as Power BI consultants too underlines that point

 

ryan
2017-07-20 16:13
So do you guys think this shift is due to tools with this power, previously exclusive to techies, are now accessible to more user?

 

javiguillen
2017-07-20 16:13
Interestingly, my experience has been that those dividing lines disappearing (although scary) can help increase adoption of tabular technology — in some cases increase the workload for Tabular developers in Corporate side of I.T.

 

bill.anton
2017-07-20 16:14
yes! i’ve had several projects that started as Power BI engagements and grew into full-blown DW/Tabular projects

 

chrisw388
2017-07-20 16:14
Yes, and I think you have to look at the licensing of those tools too: it’s not just the power, it’s the price

 

ryan
2017-07-20 16:14
It’s a good world to live in :slightly_smiling_face:

 

bill.anton
2017-07-20 16:14
Free as in beer

 

bill.anton
2017-07-20 16:15
PBI Desktop at least

 

ryan
2017-07-20 16:15
So what are some of the use cases for Tabular?

 

javiguillen
2017-07-20 16:15
where do I get free beer??

 

chrisw388
2017-07-20 16:15
If you get past the ‘free’ thing (which it’s obviously not), it is still very, very cheap

 

bill.anton
2017-07-20 16:16
especially compared to tableau and/or qlik

 

bill.anton
2017-07-20 16:17
Ad-hoc analytical reporting/queries

 

javiguillen
2017-07-20 16:17
Tabular, being the ‘big brother’ of Power Pivot and Power BI (which are sisters, I think), allows for growth beyond the limitation of those (the Power tools). A model that starts in Power Pivot can evolve in complexity, requiring much larger volumes (billions of records) and Tabular is there to step in and allow for that growth. In that sense you would speak of migration from self service BI (or corporate BI prototypes) to enterprise BI models

 

chrisw388
2017-07-20 16:18
I guess the classic use case for Tabular – and also to draw the line between Tabular and Power BI – would be when you already have a data warehouse, it contains a more data than Power BI can handle, and you’re ok with one big central model that end users either don’t want to create/update/maintain themselves or don’t have the time or skills to

 

ryan
2017-07-20 16:19
Our models can grow up… without a whole lot of new skills. Sounds valuable to me!

Features, Now and in the Future

ryan
2017-07-20 16:19
There were a couple mentions of something called “partitioning.” What is that and what is it good for?

 

bill.anton
2017-07-20 16:19
yes the Power BI “grow up story”… think I first heard that term on Chris’ blog

 

chrisw388
2017-07-20 16:20
Partitioning is the answer to one of the hottest topics in Power BI – how to do incremental refresh. In other words, how to avoid having to reload your data completely when you refresh a table, and instead only load the new data.

 

bill.anton
2017-07-20 16:20
partioning = breaking up large tables into “chunks of rows”… makes it easier to manage and maintain a large data set

 

javiguillen
2017-07-20 16:20
true. Although with the addition of M/Power Query into Tabular those lines are blurring too…. one could think of Tabular models that do not have a data warehouse supporting it. That thought is scary to many I.T. Pros, others are more open to it.

 

chrisw388
2017-07-20 16:21
And I think partitioning as a feature will be a licensing dividing line. I don’t think it will ever be in Power BI Pro, but it has been announced for Power BI Premium and of course is in the more expensive SKUs of Tabular.

 

ryan
2017-07-20 16:21
I can’t tell you how many people have asked me for that in PowerPivot/PowerBI…

 

ryan
2017-07-20 16:22
It’s something that I’d love to see Microsoft add. What are some other things y’all think MSFT can do to improve this product?

 

chrisw388
2017-07-20 16:23
Well, the number 1 thing I complain about with Tabular is the quality of the development experience in Visual Studio. It’s awful, although it’s starting to get better.

 

bill.anton
2017-07-20 16:23
Add the ability to import PBI desktop files into the on-prem version of SSAS Tabular…

 

chrisw388
2017-07-20 16:23
@bill.anton I don’t think that will ever happen – think about the problems with different versions of the engine!

 

ryan
2017-07-20 16:24
Is importing files to tabular ever a thing?

 

javiguillen
2017-07-20 16:24
I would love to see a DAX calculation lineage view that works with a simple click

 

bill.anton
2017-07-20 16:24
it’s possible in Azure AS… don’t see why it couldn’t be possible in on-prem

Power Pivot Can Turn into Tabular?

bill.anton
2017-07-20 16:25
similar to starting a SSAS Tab project via Import from PowerPivot

 

ryan
2017-07-20 16:25
Woah! Pump the brakes! I can take a PowerPivot model and just put it on a Tabular server?

 

chrisw388
2017-07-20 16:26
You can convert a Power Pivot model into an SSAS Tabular project, and you’ve been able to do that for years

 

javiguillen
2017-07-20 16:26
importing PBI files to SSAS Tabular has the advantage of enabling business solutions (developed into Power BI), to ‘upsize’ into Tabular (typically owned by I.T.). The technical part works (in Azure), the question is the process: changing “ownership” from users to I.T. must be done carefully so as to not alienate the original authors….

 

bill.anton
2017-07-20 16:27
yeah – the “strategic prototyping” idea…

 

bill.anton
2017-07-20 16:27
it’s a good one and very effective in my experience

 

javiguillen
2017-07-20 16:27
exactly! sounds fancy too.

 

ryan
2017-07-20 16:28
I’ll confess. I lied. I have imported many model’s to Tabular. But it’s something that I think is amazing enough that’s it’s worth playing dumb for

 

bill.anton
2017-07-20 16:28
stole it from you

 

ryan
2017-07-20 16:28
It plays directly into that growing up story we’d been talking about

 

chrisw388
2017-07-20 16:29
I think once the ability to import pbix files into Azure SSAS is released, this type of scenario will be even more common

 

javiguillen
2017-07-20 16:29
lol… The potential there is huge. Now, among all the published Power BI models (which in some companies can be hundreds or thousands)… which ones must be upgraded to Tabular? That goes to the topic of auditability – understanding usage patterns, which in Power BI can be done via audit logs

 

javiguillen
2017-07-20 16:30
in theory – you would consider upgrading the most “popular” models. Popularity is important, like in high school!

 

ryan
2017-07-20 16:31
So I think this is another great point on the topic of IT pro’s shifting role with these techs.

 

ryan
2017-07-20 16:31
Now, analysis is available to almost everyone

 

javiguillen
2017-07-20 16:32
Few companies have an established, ongoing audit tracking program with staff assigned to identify the “popular” models. There is lots of value wasted there…

 

ryan
2017-07-20 16:32
And IT can crowdsource for the best biz logic

 

chrisw388
2017-07-20 16:34
BI has always had to be collaborative to be effective. I think new tools like Power BI allow business analysts to take a greater role than they have been able to take in the past, increasing the chances of success.

 

bill.anton
2017-07-20 16:34
yes – and help facilitate the transition. not all PBI models will scale well just because they’re converted to Tabular… modeling and writing efficient DAX are very important for large models

 

javiguillen
2017-07-20 16:35
@ryan In my opinion the idea of crowdsourcing models is great – although in my experience it doesn’t work without planned oversight. “Somebody” must be keeping an eye on the most valuable Power Pivot/Power BI models that can be upgraded into Tabular (or integrated/merged into existing ones). Ideally this person is willing to give credit to the model authors.

 

chrisw388
2017-07-20 16:35
And of course designing good models (and getting the data for them, and writing the DAX) is difficult. It’s something that many people with a strong IT background struggle with.

Biz and IT Collaboration, Whoa

ryan
2017-07-20 16:35
Great points! So the highly technical users in IT can provide this type of oversight and optimization?

 

chrisw388
2017-07-20 16:36
Yes, definitely. But I would stress it’s not oversight – it has to be more collaborative than that

 

chrisw388
2017-07-20 16:37
Oversight suggests that IT has the overall power.

 

bill.anton
2017-07-20 16:37
agreed… collaboration and engagement

 

bill.anton
2017-07-20 16:37
both of which facilitate adoption

 

javiguillen
2017-07-20 16:38
Yes – I often recommend to I.T. leaders to have an “adoption management” program in place which includes harvesting Power Pivot/Power BI models that have potential for growth and service to a wide audience. Here also resides the opportunity for collaboration

 

bill.anton
2017-07-20 16:38
which has plagued many BI projects over the years… “If you build it they will come” is not the right approach

 

javiguillen
2017-07-20 16:38
“handing a license to a user” is also not the right deployment approach

 

bill.anton
2017-07-20 16:39
very true

 

chrisw388
2017-07-20 16:39
It’s finding the balance. Who else remembers that Power Pivot was originally codenamed “Project Gemini”, with the twins being IT and users?

 

bill.anton
2017-07-20 16:39
@javiguillen – I think everyone should see your presentation on “adoption management” (can’t recall the actual title anymore) but its a good one

 

bill.anton
2017-07-20 16:40
haha

 

chrisw388
2017-07-20 16:40
Though of course twins, like most siblings, spend a lot of time fighting

 

bill.anton
2017-07-20 16:41
in some sense, I’m more comfortable seeing IT and Business Users fight as opposed to not speaking

 

javiguillen
2017-07-20 16:41
In the past, I used to hear a lot the term “shadow I.T.”, which obviously has a negative connotation. I think now there is an opportunity to understand those tech savvy business users as “super users”, instead – and they serve a huge purpose in terms of delivery of tactical/short term solutions (with lots of value to the business), which also serve as prototypes (blueprints) for potential corporate BI features that the enterprise I.T. team can ‘adopt’ from them (via migration of Power BI to Tabular is just model comparison via tools like BISM Normalizer)

 

bill.anton
2017-07-20 16:42
there it is!

 

chrisw388
2017-07-20 16:42
Yes, and lack of communication is the most common problem. The tendency to put IT in its own building, miles out of town, doesn’t help. Or even to think that BI, like other IT, can be outsourced.

 

ryan
2017-07-20 16:42
Many of our readers (myself included) fit into that category!

 

bill.anton
2017-07-20 16:43
I started my career in that category… a team sent in to get stuff done and undermine the broader corporate IT reporting group!

 

bill.anton
2017-07-20 16:43
we added value but also made a mess

 

ryan
2017-07-20 16:43
That idea of prototyping for Tabular was extremely powerful as we spoke both the languages of biz and IT

 

javiguillen
2017-07-20 16:43
@bill.anton I know! interesting we are in a fact/number driven field but yet sometimes we don’t stop to check adoption hard numbers….. is the model I created being actually used by people? (usage is an indirect yet easy form to measure adoption)

 

chrisw388
2017-07-20 16:44
And in the same way that the lines between the different classes of software are blurring, the lines between IT people and business people are blurring too. In fact I think a lot of “IT” people in the Microsoft world didn’t start off as IT people, and certainly didn’t study anything like Computer Science at college/university.

 

ryan
2017-07-20 16:44
I think a lot of organizations are struggling with that problem @bill.anton. How can they effectively deploy people like us?

 

bill.anton
2017-07-20 16:45
Well I think the solution is along the lines of what has been mentioned in this conversation… if our little rogue reporting team had of partnered up with the corporate IT reporting group… we would have been much more successful

DAX, DAX, #DAXIsLife

ryan
2017-07-20 16:46
So I’m picturing a perfect world where end users work with Super Mutant DAX users who work with IT to get from the trenches to the servers (editor Austin jumping in here, thrilled to see the first public use of my “Super Mutant” persona. Carry on.)

 

javiguillen
2017-07-20 16:47
@chrisw388 I wonder if its line blurring or possible role re-definition? For example, Business Analysts previously needed to know mainly how to document business requests into technical terms. Now days I see a lot of Business Analyst jobs requiring SQL knowledge and even Power Pivot/Power BI (or similar tools)

 

ryan
2017-07-20 16:47
It seems like Tabular being in the picture helps with that

 

bill.anton
2017-07-20 16:48
@ryan i think that’s pretty optimal… course finding those Super Mutant DAX users is easier than it sounds…

 

javiguillen
2017-07-20 16:48
@chrisw388 yet their intent is still to be the liaison between the business and I.T.; not to become I.T. developers…. (that is what I have observed…) Sorry for the multiline comment :slightly_smiling_face:

 

javiguillen
2017-07-20 16:49
what is a Super Mutant DAX user?

 

chrisw388
2017-07-20 16:49
Having Tabular and Power Pivot and Power BI as options means that when the time comes, it’s straightforward to convert something the business has built into something the IT department can support. It’s not like it was in the past when converting an Access solution to something corporate BI-like would take months.

 

ryan
2017-07-20 16:49
The “Shadow IT,” the DAX ninjas, the people with the data gene!

 

bill.anton
2017-07-20 16:49
someone who doesn’t use the new Quick Calcs and/or someone who has read (and understood) the “purple book”

 

bill.anton
2017-07-20 16:50
purple book = the Definitive Guide to DAX by Marco/Alberto

 

ryan
2017-07-20 16:50
I think there’s still a lot to be unpacked and learned with these tools. They are really shaking the BI world up!

 

chrisw388
2017-07-20 16:51
How many people have *really* understood the purple book…?!

 

ryan
2017-07-20 16:51
Case and point!!! hahaha

 

bill.anton
2017-07-20 16:51
ahah… I’ve read it 2.5 times and still not sure I *really* understand it

 

javiguillen
2017-07-20 16:51
Ahh… the purple book….. I recommend not reading it at night after a long day at work

 

ryan
2017-07-20 16:52
:joy:

 

chrisw388
2017-07-20 16:52
@javiguillen unless you want to start dreaming in DAX?

 

bill.anton
2017-07-20 16:52
it will melt your brain if you’re not prepared

 

javiguillen
2017-07-20 16:53
I did dream in DAX once…. I was trapped between FILTER and CALCULATETABLE. I’m serious. I think it went for hours

 

ryan
2017-07-20 16:53
So what are some of the new things going on in the Tabular world?

 

chrisw388
2017-07-20 16:53
The #1 new thing for me is M integration for data loading

 

ryan
2017-07-20 16:54
@chrisw388 couldn’t agree more on how awesome this is!

 

bill.anton
2017-07-20 16:54
yes… sadly I’ve come to the conclusion that I need to learn this language…

 

javiguillen
2017-07-20 16:54
M is huge. I also love the new object level security, to hide tables and columns.

 

chrisw388
2017-07-20 16:54
@bill.anton it’s a lot more fun than DAX, I think

 

ryan
2017-07-20 16:54
Let’s follow that thread a bit

New Superpowers

ryan
2017-07-20 16:54
What new powers does this give Tabular?

 

javiguillen
2017-07-20 16:54
uh oh….

 

bill.anton
2017-07-20 16:54
import from PBIX files

 

chrisw388
2017-07-20 16:55
It also allows access to more data sources (and soon custom data sources), and also more DirectQuery data sources too

 

bill.anton
2017-07-20 16:56
yes – the expansion of potential data sources is huge!

 

chrisw388
2017-07-20 16:56
I don’t think it should be used for ETL purposes though, at least not in Tabular

 

chrisw388
2017-07-20 16:56
ETL = Extract, Transform & Load

 

bill.anton
2017-07-20 16:56
some would argue it alleviates the requirement for a DW and ETL

 

javiguillen
2017-07-20 16:56
You can do data prep directly in the model. So the scenario above, where I.T. usually conceptualizes Tabular as being on top of a nicely developed data warehouse may not always fully apply anymore. The world is more fluid than that now.

 

chrisw388
2017-07-20 16:56
@bill.anton those would be the people who are about to learn the hardware about the benefits of building a DW

 

ryan
2017-07-20 16:57
I’ve had instance were I had to do a lot of IT-esque work to build the back-end data warehouse and then the Tabular on top of it. Could this be the end for having to do all that extra work?

 

chrisw388
2017-07-20 16:57
DW = data warehouse

 

ryan
2017-07-20 16:57
You guys are too good!

 

ryan
2017-07-20 16:57
That’s why youre the experts :slightly_smiling_face:

 

bill.anton
2017-07-20 16:57
flattery will get you everywhere 😉

 

javiguillen
2017-07-20 16:57
@ryan possibly, at least in some scenarios. But just the thought can be scary for some.

 

ryan
2017-07-20 16:57
Why is that?

 

ryan
2017-07-20 16:57
(I agree)

 

chrisw388
2017-07-20 16:57
Well, you could either build it upfront (which has its own disadvantages) or try to avoid doing so and then do it in a less efficient, more painful manner later on

 

javiguillen
2017-07-20 16:58
because I.T. traditionally likes to think in terms of layers — the data warehouse, the semantic layer, the reporting layer

 

bill.anton
2017-07-20 16:59
it would be nice if you could import the PowerQuery/M piece into an SSIS project (or enterprise ETL solution)… just as you can import the PowerPivot piece into a Tabular model…

 

ryan
2017-07-20 17:00
Now were cookin with fire

 

chrisw388
2017-07-20 17:01
Well it was mentioned (maybe by mistake) at the Data Insights Summit that it will soon be possible to use Power Query to load data into the Common Data Service

 

ryan
2017-07-20 17:01
Lets take that dive deeper into the nerd pool. What else gets you guys excited as experts in this field?

 

chrisw388
2017-07-20 17:01
So maybe the Common Data Service will become something like an easy-to-use DW in future? I don’t know.

 

bill.anton
2017-07-20 17:02
BISM Normalizer getting the adoption it deserves!

 

javiguillen
2017-07-20 17:03
and having the semantic (tabular model) layer and the ETL (data prep) layer in a single object (a Tabular model) breaks away from that architecture. Although reality (and business value) may fall somewhere in between; I have worked with I.T. department that modularize their data management program so much, that for a small reporting change all data must first pass through all layers (data lake/data warehouse/cube/report), increasing the price tag tremendously even for a tiny data addition that impacts only a single report

 

chrisw388
2017-07-20 17:04
I’m very excited about the possibilities for M custom data connectors. In fact I’m going to spend all this evening working on one!

 

javiguillen
2017-07-20 17:05
I’m pretty excited about BISM Normalizer too – it allows for merging and consolidating of Tabular models

 

ryan
2017-07-20 17:05
WHat is the BISM Normalizer and how does it fit into the picture?

 

bill.anton
2017-07-20 17:06
the CustomVisuals and CustomCalculations in the Office Store is another one I’m excited about

 

javiguillen
2017-07-20 17:06
as well as Azure Analysis Services, given it makes it so easy to quick have a Tabular environment without having to worry about infrastructure too much

 

bill.anton
2017-07-20 17:07
yes – it (Azure AS) still seems a bit pricey though

 

chrisw388
2017-07-20 17:08
BISM Normalizer is a tool for comparing two SSAS Tabular databases (or projects), so you can see what the differences between them are and make partial updates.

 

chrisw388
2017-07-20 17:09
It’s free too. http://bism-normalizer.com/ has all the details.

 

ryan
2017-07-20 17:09
A great point that orgs can use both on-prem and Azure (cloud) instances of Tabular.

 

javiguillen
2017-07-20 17:11
true. Although things can get a bit confusing with Power BI Premium, which can scale to large volumes and in the future will allow for incremental processing. Pricing is a consideration, but assuming a company already owns Premium and Tabular, it may not always be straight forward to decided where to place your model(s)…

 

chrisw388
2017-07-20 17:12
I agree, I’m still not clear in my own mind when to use Premium and when to use SSAS Tabular, and when to use both.

 

bill.anton
2017-07-20 17:13
agreed… re: Premium w/ large PBI model vs Azure AS Tabular… a lot of that has to do with my own limited understanding of performance capabilities of cloud resources

 

chrisw388
2017-07-20 17:15
I think no-one knows yet. We just have to test these options in real-world projects. It’s a shame you can’t test Premium easily without paying $4999 for 12 months.

 

bill.anton
2017-07-20 17:16
yeah that does suck… would be nice to have a little insight via MSDN subscriptions (similar to Azure Credits)… perhaps that will change once the smaller Premium units are available (the ones for Embedded)

 

ryan
2017-07-20 17:17
I think MSFT would even agree that they are trying to figure out how all of this awesome tech fits together

 

javiguillen
2017-07-20 17:17
its a fast changing world :slightly_smiling_face: although I tend to think that within these fast changes, something remains – the need for an SSAS/Power BI/Power Pivot deployment “strategy”. In short, how do we account for both the order and meticulous design proper of I.T. plus the quick iterations and tribal knowledge proper of business users. The need to have a strategy addressing that remains fairly constant (and the thoughts around it), even with a fast pace of technical change

 

chrisw388
2017-07-20 17:17
And that’s what makes all of this so exciting!

 

ryan
2017-07-20 17:17
Its a good problem to have!

Wrapping Up

ryan
2017-07-20 17:18
So, you guys have a soap box moment here to talk to users interested in Tabular. What would you tell them?

 

bill.anton
2017-07-20 17:18
yes – its a really good time to be working w/ Microsoft BI tools!! the outlook is way better than it was ~4-5 years ago

 

chrisw388
2017-07-20 17:20
I would tell users that modelling is everything. The better model you have, the more likely you are to be successful. You can’t just put a Tabular model on any relational database and expect it to work well.

 

bill.anton
2017-07-20 17:20
don’t skimp on learning dimensional modeling (search: Ralph Kimball) … it might seem like overkill for an Information Worker, but if you can wrap your head around it, you’ll be nearly unstoppable

 

bill.anton
2017-07-20 17:20
haha

 

javiguillen
2017-07-20 17:23
Agree with @chrisw388 completely. Although users are usually attracted to the visuals, in reality that is the easy part. If your data is properly formatted and modeled, reporting on it is can be a pain-less experience.

 

javiguillen
2017-07-20 17:24
So I would tell them to either study the modeling principles, or find a friend to do that work for them :slightly_smiling_face:

 

ryan
2017-07-20 17:24
Great advice!

 

ryan
2017-07-20 17:24
Thank you guys so much for stopping by and sharing your expertise!

 

chrisw388
2017-07-20 17:25
No problem, it’s been a pleasure!

 

bill.anton
2017-07-20 17:25
thanks again for having me

 

javiguillen
2017-07-20 17:25
Thank you @ryan ! it was fun. It was great chatting with you all!

 

javiguillen
2017-07-20 17:26
I don’t often get the pleasure of chatting with @bill.anton and @chrisw388 , particularly on casual Thursdays – it was great to see you both.

 

  Subscribe to PowerPivotPro!
X

Subscribe

Austin Senseman

Austin spent the last five years using Modern Excel in the field to solve analytical challenges for the financial services industry, first at BBVA and later at Harbert Management Corporation. As a former analyst, Austin is focused on enabling other data professionals to experience the joys of Power Pivot & Power BI. Austin is a CFA Charterholder, former CPA, Excel Power User, and SQL Server Professional, whose involvement in data/analytics projects lowers the communication costs between business users and IT professionals and helps organizations get to solutions faster. 

This Post Has 5 Comments

  1. Ryan, Chris, Javier, Bill and Austin: Many thanks for the time to prepare and co-ordinate this dialog! There’s definitely a lot to chew on here (like getting two bagels with the one cup).

    Definitely appreciated the talent pool in this dialog and the different perspectives. Would like to see more posts like this.

    1. I work for a very small but really innovative company who have been working on a knowledge project for almost a year. I’m a beanie and my partner in crime is a DBA. We’ve both basically just read a bunch of blogs, read Rob, Avi and Ken’s book (I also bought the purple book but don’t understand it yet) and then jumped in. We’ve got SSIS pulling data from our cloud CRM and payroll system into SQL (my IT friend did this bit), and then we’re pulling data from this SQL DB and our ERP SQL DB via Power Query and Power Pivot restoring these PP models into SSAS Tabular and scheduling refreshes with Power Update (this is my bit of the project) and consuming the data in Power BI and Excel (I do the PBI bit too). We’ve got a dedicated server (I think it’s a virtual server?) running the refreshes overnight. I’m lucky that our business was willing to spend a hunk of cash on the SSAS licence. My IT buddy would love this partitioning stuff as we would like to move to more frequent refreshes for some of our models, but IT don’t want to slow down the ERP for users by hauling out all the data multiple times a day. Is there a book or other resource you’d recommend so we could work out how to do this stuff straight in SSAS? Is it realistic to unleash an accountant on this stuff or would we be better to keep going with our current method? My mate in IT wants me kept well clear of anything that would let me delete a table in the ERP DB (sometimes I like to hit random buttons to see what they do). Would love to hear your thoughts. I’m really enjoying this blog series – really good info!

  2. Great dialogue. I particularly liked this comment: ” I think now there is an opportunity to understand those tech savvy business users as “super users”, instead – and they serve a huge purpose in terms of delivery of tactical/short term solutions (with lots of value to the business), which also serve as prototypes (blueprints) for potential corporate BI features that the enterprise I.T. team can ‘adopt’ from them (via migration of Power BI to Tabular is just model comparison via tools like BISM Normalizer)”.
    I think that PowerBI is the tool that we can give to interested users for them to ‘play’ with. After all, they are the consumers of the data, and understand what the desirable insights are. What they don’t have (yet) is access to a tool in which they can mix and match data to discover connections and correlations. The IT group (like the HR group, and all the other support groups) can call themselves “Business Partners” but no one is going to understand the data and its’ potential like the business expert. Great partnering should involve giving these users the tool set, and showing them how to use it.

Leave a Comment or Question