Build Model in Analysis Services vs. Power BI

[jblackshear]

Hi everyone,

A while back, someone said somewhere (a conference session or a Webinar - I don't remember) that you could build your entire multi-source model in SSAS Tabular rather than in Power BI. That makes sense because both use the same language. But I have been unable to find any more in-depth information about this.

I love the ability in Power BI to bring in a variety of data sources - that is the primary reason we decided this would be our analysis and graphical information tool. But I am concerned that I will not be able to maintain development of a large number of focused models. If I could build an organization-wide tabular model in Analysis Services, that could be accessed by Power BI, SSRS, or Excel, that would be fantastic.

Does anyone have experience with modeling their data this way? I would love to know if this is something to pursue - and to know it before I commit too much time to a multitude of mini-models in Power BI.

Thanks,

Judy Blackshear
Business Intelligence Developer

Texas Electric Cooperatives

Austin, Texas

[Greg_Deckler · Accepted solution]

Yes, you can absolutely do that. Generally when you do it that way, you build out the SSAS tabular model using something like SSIS to ingest the information and then you connect live (DirectQuery) to the model in Power BI Desktop.

[jblackshear]

Thank you!

So my limitation on data sources I can incorporate into my model will be whatever SSIS components are available for sale, or what I can build. Not excited about that part - I really like and appreciate the many Power BI connectors available. But it still may be a good plan for us overall.

I have another, related question (although I fear I know the answer). Can additional measures be created within the Power BI model when using Direct Query to Analysis Services?

[Greg_Deckler]

Yes, you should be able to create additional measures when you connect live to an SSAS tabular model. Perhaps one of these days there will be a unified connector framework where everything uses Power Query...wouldn't that be awesome?

One other note, you can import a Power BI Desktop model into Azure Analysis Services if you want to use SSAS that way.

[EfficientComp]

Not anymore.

https://azure.microsoft.com/en-us/updates/azure-analysis-services-web-designer-to-be-discontinued/

[jblackshear]

Thank you @Greg_Deckler! Now that I have found a knowledgeable resource, I'm going to pick your brains until you say "enough!". 🙂

Can you point me to some good current resources for studying this further?

"Semantic Data Model" is the term I had in mind when I first began in this job, but I had not seen it used by Microsoft in relation to their products until yesterday. Finally I turned up the right search terms and clicked through the right links to end up at "Semantic BI Model" in relation to Microsoft products. But those articles are from 2012 - so I question whether that's still a useful term when discussing the capabilities of Microsoft products. 

I am annoying the heck out of executives, managers, and others at my place of business, asking for clarification and details, and nitpicking. But I view that as the most important part of my job, and the best of my several capabilities I bring to the job. I want to build a good Semantic Data Model that can be used as the data source for Power BI, SSRS reports, Excel, with a lot less direct IT involvement.