Webinar Q&A: Getting Started With Analysis Services

I recently presented a free webinar for Pragmatic Works Free Training on the T's about Getting Started With Analysis Services. We covered some of the differences between the two project types at a high level then built one of each to give everyone an idea of how difficult and how fast each could be developed. If you attended I want to say thank you. It's always appreciated when people come out for these sessions. If you were unable to join the session here is a link to the recording that you can watch for free. The Q&A can be found below.

Q: Is it easy to convert from a multidimensional to tabular project (or even tabular to multidimensional)? A: No. You will need to rebuild the project in the version you want to switch to. They operate and are built very different. Q: How well does SSAS interact with Microsoft PDW? A: Obviously this is a little more specific question. My short answer is that SSAS on top of PDW works just like it does against SMP SQL or any other relational database for that matter. The problems start to appear when you run SSAS in ROLAP or DirectQuery mode though. I have a fairly large SSAS multidimensional solution running in ROLAP mode built on PDW for my current client and it is working great. In the current version of PDW (v2 AU .5) the two things I would say though are to avoid parent child hierarchies since the T-SQL issued to satisfy those are not currently supported on PDW and put your fact table in columnstore, it makes a big difference. Q: How does the “find related tables” button know which tables to bring in? A: You must have constraints built into your data model. Simply putting a primary key on each table is not enough. Q: The thought of building a data warehouse is daunting, I understand the concepts but have never built one. Do you have any advice or resources that might help increase my chances of creating a useful data warehouse the first time? A: There are a lot of really good articles on SQLServerCentral.com and a ton of webinars on PragmaticWorks.com that are free just like the one I delivered this week. Before I go any farther, I work for Pragmatic Works and I do not get commission or referral bonuses or anything like that. That being said, I don't like to push the company products on my blog because I don't want to make this a giant advertisement. In this case I will make an exception. Pragmatic Works has a training offering that fits exactly what you are asking and we call it a Business Intelligence Bootcamp. It's a 1 week, in person training session that we do at Microsoft offices around the country. We go through data modeling, ETL, SSIS and then reporting and show you how all the pieces fit together. The great thing about it is that you get to take the project files back with you for reference and we show you how decisions along the way affect other parts of the project. For instance, why surrogate keys in your data model are important when you get to building your SSAS cube. Q: How do you add a second data source when using the Tabular model? A: To add more data sources just click the same button in the top left corner of the screen that we used to add the first set of tables. You can have as many data sources in the model as you would like. Q: Could you comment on the differences between SSAS Tabular and PowerPivot for SharePoint? A: A lot of the features are the same between the two. In a nutshell you can think of Tabular as the big brother of PowerPivot. Security is more robust, enterprise backup/DR strategy, model management and refresh is better (partitions) and it can handle much more data. PowerPivot workbooks uploaded to SharePoint are limited to 2GB in size, you don’t have to worry about that with Tabular. A lot of companies are using PowerPivot to prototype or get the projects started, then use the Import PowerPivot Workbook option I mentioned to create the full size Tabular model. Q: Is Tabular in Excel and what version of Windows is required? A: Tabular is not in Excel, PowerPivot however is in Excel. The minimum to use PowerPivot is Excel 2010. The system requirements can be found here. I’d rather link to it since Microsoft tends to keep those pages up to date very well and there are constant product changes between Excel and SQL Server. Minimum requirements for SQL 2012 though is Windows Vista SP2 or Windows Server 2008 SP2. Q: Can there be multiple fact tables (measure groups) in a single cube? A: Absolutely. I just showed one because of time constraints. There are certain situations that actually require additional measure groups to be added, such as many to many relationships. Q: When was Tabular introduced? When was PowerPivot introduced? A: Tabular was first introduced in SQL Server 2012. PowerPivot was first introduced with Excel 2010. Q: How big is too big for tabular? A: How big is your server? This is the classic “it depends”. Tabular is built to run with data in memory. If your server only has 4 GB of memory and you are wanting to build a model that has 2TB of data you are likely going to be unhappy. That’s not to say this won’t happen with Multidimensional as well, because it will, but it has lower memory limitations because it is built to have preaggregated values created at processing time and stores the data differently. I’d recommend watching Devin Knight (Twitter|Blog) and Dustin Ryan’s (Twitter|Blog) session on “Choosing the Right Analysis Services: MOLAP vs Tabular” (Free Recording) since they touch on this issue.

Webinar Q&A: Getting Started With Analysis Services

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog