Here were some of the questions from today's webinar. The webinar about Power Pivot can be seen here. http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=635
We had a boatload of questions and I tried to answer them all but here are some of the key ones to remember.
Q: What version of Office can I use Power Pivot on?
A: You can use it with Office 2010 or greater. Office 2010 requires a plug-in to be installed. The easiest place to find the plug in is at http://www.powerpivot.com. In Office 2013, it's included with the core product with Pro licenses and greater.
Q: Does Power Pivot have the same row limit as regular Excel.
A: No, there's not a published hard limit but there is a practical one that you might experience. Keep in mind that Power Pivot is an in-memory process, which makes it so fast. That said, RAM is your ultimate limiting factor. Power Pivot will compress the rows by 90% in most cases and that file size after compression shouldn't exceed your RAM size.
Q: Can you share data sets in Power Pivot?
A: Yes, you can most certainly share the reports by email, network share or best of all, SharePoint. Why SharePoint is best is it can negotiate the data refreshing for you. Users won't need access to the underlying data to see the report but they will need access to the data sources if they want to refresh the data.
Q: Are incremental refreshes supported in Power Pivot?
A: No, you can't really do incremental refreshes *at this time* in Power Pivot. This can however happen in a MOLAP Analysis Services cube. In Tabular Analysis Services cube (which resembles Power Pivot but on a server), you can refresh at the partition level.
Q: Can I get the data set?
A: We used an election results data set from the FEC (Federal Election Commission). You can find my data set (Access 97) here: Data Source Used in Webinar: ElectionSourceData.zip. It took days to get this data working the way we show since the FEC's data sources weren't clean at first. Please attribute Pragmatic Works if you use it :).
By Adam Jorgensen and Tim Moolic
Today’s businesses are experiencing exciting growth. This renewed growth requires the need for improved strategies to scale and grow while remaining flexible and agile. Gartner and Forrester are backing this up with their push to evaluate the cloud market as a top strategic priority for today’s modern business. Join Tim Moolic and Adam Jorgensen COO and President of Pragmatic Works as they bring their decades of experience to discuss why and how the modern business re-platforms at a fraction of the effort and cost by using the cloud for data, applications, and analytics. This session will include specific customer cases and high level customer architectures along with a C-Level guide to the state of the cloud and it’s benefits for your organization.
To view the recording of this session please visit
To download the slide deck please click here.
By: Devin Knight
I hope you were able to attend my free webinar on Bringing Power BI Q&A to your Organization on April 15 2014. If you weren’t you can now watch the recording here.
Traditionally following a webinar I try to post a quick Q&A session for everything I was not able to answer in the allotted time.
Q: Does Power BI require SharePoint Online or can it work with public facing on premises SharePoint?
Yes, Power BI sites and Power BI Q&A are only available if you’re using SharePoint Online. Public Facing traditional SharePoint servers do not have the ability to add the Power BI functionality.
Q: If you create a PivotChart or Power View report, can Q&A show that chart when a user searches something relevant to the chart?
No, currently Power BI Q&A can only create Power View reports only the fly. It cannot reference preexisting reports and display those. You can however influence the way your data is presented by giving proper relationships, data types and data categories. Once a Power View report is created on the fly you can change the visualization easily by selecting the the chart type you would prefer.
Q: Can I export the results to Excel and/or PowerPoint?
The current use of Q&A is as a quick ad-hoc analysis tool and does not have an output like this although I think this is an interesting thought.
Q: Can i upload an Excel file to the Power BI site which is data connected to an SSAS source (locally hosted)?
You can as long as the SSAS source exposed through Power Pivot. If you want to refresh any locally hosted data sources then you must configure the Data Management Gateway to allow local data sources to interact with the Power BI cloud.
So the normal concept of index on a particular column is a key and all rows that have that key in that column. So, with columnstore do we create index on row instead of column?
Columnstore is created across all, or most columns, in a table. Each column is stored individually, so the order of the columns makes no difference. Each column can be accessed independently from other columns in the table.
A typical fact table is mostly comprised of keys and measures. So if you cannot include keys in the columnstore for the table would you architect the warehouse without foreign key relationships and handle them through a meta-data layer for ad hoc reporting?
Yes, create the data warehouse without foreign key relationships and enforce these business rules through the ETL processes. Unlike, OLTP databases, data warehouses typically have a limited number of processes that change data in each table. So ensuring these business rules are followed is much more simple.
Can you explain how data is stored between rowstore and columnstore index with some example data?
I would primarily recommend re-watching the first portion of the webcast again. That said, in a rowstore, SQL Server stores all of the columns for a row together. While in a column stored all of the rows fora column are stored together.
Are columnstores linked to SQL Server edition somehow? are they available in standard edition?
Yes, columnstore indexes are only available in Enterprise edition. As a side note, you can leverage them in SQL Server Azure VMs since they come pre-installed with Enterprise Edition.
Could you please recommend a resource (book) which we could useto practice columnstore indexes?
Currently, the only resource I am aware of is Expert Performance Indexing for SQL Server 2012.
By: Jason Strate
As a DBA, how can I find good column to create a clustered index on a table given that I don't know my workload on the table.
I would start with the primary key on the table. That's often one of the best places to start. From there it’s a matter of how you are using the table, or plan to. A great resource would be the session I did last year, Choosing Your Clustered Index.
Can you please discuss the difference between clustered and non-clustered indexes?
Non-clustered indexes represent secondary access patterns for accessing your data, where clustered indexes are the primary path for data access. The webcast How Do Non-Clustered Indexes Improve Performance? can provide some additional information that would be useful.
Could you please provide a good book we could use as reference/review?
The best book out there on indexes would be my book for SQL Server 2012 titled, Expert Performance Indexing for SQL Server 2012.
Is there any real time scenario, where heap is better than clustered index?
If you plan to load a table and then query the data once or twice. It often takes longer to import and maintain the clustered index that it would be to just use a clustered index.
Is there a general rule or typical scenarios when would youconsider using a non-clustered index, or is there one?
Non-clustered indexes are intended as alternative paths to data on a row. The clustered index key won't always be the best way to find every row every time. For more information, check out - How Do Non-Clustered Indexes Improve Performance?.
On April 3rd, I gave a webinar for Pragmatic Works Training on the Ts on the new SSIS Project Deployment Model introduced with SQL Server 2012. Be sure to check out the recording if you missed the session. If you would like a copy of my slides and code, it is here. Here are the top 5 questions from the session:
By: Kevin Kline
Ever wanted to convince the boss try something new, but didn't know where to start? Ever tried lead your peers only to fail to achieve your goals? This session teaches you the eight techniques of influencing professionals, so that you can innovate and achieve change in your organization.
View the recording here.
Download the PowerPoint here: UG - Convince Me Persuasion Techniques That Get Things Done (Short Form).pptx
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.
By: Devin Knight, @knight_devin
I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on March 13, 2014. If you weren’t you can now download the recording and slides.
I usually like to post follow up questions and answer that I wasn’t able to address during the webinar. Here’s several that may help you!
Q: Can you explain quickly how to use Power View on SharePoint 2010?
To use these together you must have SharePoint 2010 SP1 Enterprise and SQL Server 2012 Enterprise or BI Edition. Unfortunately, there’s not a quick way to describe the rest of the configuration so here’s a post that may help.
Q: What devices are supported by mobile BI
The Mobile BI will be available on Windows 8 and Windows RT devices. There is also a native Mobile BI app for iPad under development.
Q: Are MS planning to remove the dependency on Silverlight for Power View (etc.) thus making it possible to render on iPad?
Yes! There are actually some public betas of Power View working on HTML 5 instead of Silverlight. This will be the major enhancement that allows support for the mobile BI app on IPads
Via Mark Vaillancourt's personal blog: http://markvsql.com/