|
|
|
|
SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus
-
One month ago I wrote about my sessions at PASS Business Analytics Conference 2013, in Chicago, IL on April 10-12, 2013. If you still have not registered, you can save $200 by using the code BAC228BL and you should hurry up, because there is another discount if you register within March 15, 2013. If you are too lazy to click on the previous post, I will speech in two sessions: - Modern Data Warehousing Strategy
- Self-Service Data Modeling
And now that Data Explorer Preview has been made public I can disclose that Data Explorer will be covered in my Self-Service Data Modeling session! I thought about writing an article about Data Explorer, but there is already a good coverage and I suggest you to read these blogs:
|
-
There is a new whitepaper from Microsoft, Microsoft BI Authentication and Identity Delegation, which describes all the authentication and delegation scenarios with Microsoft BI technologies: - Personal BI Scenarios (Excel)
- Team BI Scenarios (SharePoint)
- Corporate BI Scenarios (Reporting Services, Analysis Services)
- Federated BI Scenarios (Multi-Forest AD, Extranet, Cloud)
This is the new reference whitepaper to correctly plan and configure the security environment of a BI solution based on Microsoft BI stack.
|
-
Rob Collie Ken Puls (thanks Bob!) wrote a very interesting post about the PowerPivot for Excel 2013 licensing.
Short recap: - PowerPivot for Excel 2013 is only available in a few editions of Excel/Office (more details here)
- If you want to buy a perpetual license of Excel with PowerPivot, you need to be included in a Volume Licensing program
- The easiest way to enter a volume licensing program is buying 5 licenses for Microsoft products, even different ones
- The cheapest license you can buy is around 7$ (i.e. Microsoft DVD Playback Pack for Windows Vista Business)
- Buying 4 of them grants you the ability to buy Office 2013 Pro Plus license as fifth product to enter the volume license agreement
This really does not make any sense to me, and like everyone I hope Microsoft will fix the licensing issues that could limit the adoption of PowerPivot for Excel 2013. In the meantime, there is a way to get the product spending just 30$ more.
|
-
I recently published a video session on Project Botticelli web site regarding querying in DAX. You can write DAX queries to extract data from both PowerPivot and SSAS Tabular models. This could be particularly useful in three scenarios: - Reports in Reporting Services
- Excel tables (used to populate other PowerPivot data models, for example)
- Reports embedded in custom applications (i.e. ASP.NET pages)
Today there is a lack of tools to help you writing a DAX query, probably the best choice is DAX Studio, which I used in the video, but it has to be enhanced and completed. I hope to be able to work on that, but remember that other volunteers are welcome in a Codeplex project! In this video I cover in particular the syntax of EVALUATE, CALCULATETABLE, SUMMARIZE, ROLLUP, ADDCOLUMNS, and ROW functions. The video is available to Project Botticelli subscribers. 
|
-
As I mentioned a couple of weeks ago, I and Alberto are starting to travel again, delivering SSAS Tabular Workshop and other free community events around Europe. This is the updated list of our next dates, including community events: - Online – SSAS Tabular Workshop on February 27-28, 2013 (17-24 CET)
- This workshop is delivered online – you can attend wherever you are
- Timeslot is late afternoon-evening for Europeans, more appropriate office time for US market
- Aarhus, Denmark – SSAS Tabular Workshop on March 12-13, 2013 (9-17 CET)
- Alberto Ferrari will be the teacher in these dates
- You can still register here
- Aarhus, Denmark – Excel 2013 PowerPivot in Action on March 12, 2013 (17:30-19:30 CET)
- Free community event where Alberto will show the capabilities available in Excel 2013 using PowerPivot and Power View
- More info here
- Utrecht, The Netherlands – SSAS Tabular Workshop on March 25-26, 2013 (9-17 CET)
- Marco Russo (me!) will be the teacher in these dates
- You can register here (early bird expires on March 11, 2013)
- Veenendaal, The Netherlands - Querying and Optimizing DAX on March 25, 2013 (18-21 CET)
- Oslo, Norway – SSAS Tabular Workshop on April 8-9, 2013 (9-17 CET)
- Alberto Ferrari will be the teacher in these dates
- You can register here
- Milan, Italy – PowerPivot Workshop Full on April 17-19, 2013 (9-17 CET)
- Alberto will be the teacher in these dates
- Be careful: this workshop will be delivered in Italian language – a good excuse to practice your Italian visiting Italy for work reasons!
- You can register here
Other workshops will follow in May and June in Finland, UK, Belgium and Sweden – a complete list of Tabular Workshop here and PowerPivot Workshops here.
|
-
I and Alberto Ferrari will speak at SQLBits XI (Nottingham, UK – May 2-4, 2013) and there are many good reasons to join us there, especially if you are interested to DAX! Here are our sessions: - Thursday, 02 May 2013 – Training Day - From 0 to DAX
- The entire day is dedicated to learning DAX, starting from the syntax and going forward with more complex syntaxes for both expressions and queries in DAX.
- Technical level: the goal is to be introductive, explaining the important concepts in DAX (such as filter context, row context, context transitions) and covering the usage of the most common DAX functions.
- Target audience: Information Workers interested in exploiting the many interesting features of PowerPivot for Excel 2013 and BI developers who want to learn the DAX language.
- In practice: if you started using DAX two years ago and created several projects in Tabular and/or PowerPivot with complex calculations, this is not the workshop for you (read about an Advanced DAX Workshop later in this post). But if you just started your first Tabular or PowerPivot projects and you are still wondering what CALCULATE is all about, then this is definitely the right training for you!
- Friday, 03 May 2013 – 10:50am – Modern Data Warehousing Strategy
- How to design a Data Warehouse in 2013? Should we change something considering the introduction of in-memory technologies such as xVelocity? Is Self-Service BI affecting the way we design and implement a Corporate Data Warehouse?
- This is not a session about a specific feature of a product, but features and products have their effects on the data warehouse design. We’ll stop one hour trying to understand what changes in the big picture, affecting also our day-by-day job.
- Friday, 03 May 2013 – 2:40pm – Inside xVelocity InMemory Engine (VertiPaq)
- Do you use PowerPivot and/or Analysis Services Tabular? They use the same engine (xVelocity/VertiPaq) and you should know how it works.
- This session will show how xVelocity/VertiPaq works, how it compresses and stores data and why it is so fast answering to your queries.
- Why this is important? Because it is geeky! And because this knowledge will help you optimizing storage and DAX queries.
- Saturday, 04 May 2013 – 10:50am – DAX Query Engine Internals
- DAX is amazingly fast. But some query might be not fast enough.
- Do you want to know how to optimize your DAX query? This session is for you.
- Knowing the internals of xVelocity InMemory engine is just the first step (see Friday’s session). Then you need to apply this knowledge to DAX and understand why different DAX syntaxes for the same result might have different performance.
- Understanding cross-filtering is a key to master DAX optimization. The more you know DAX, the more you will appreciate this session!
As I said before, the training day is an introductive course. If you already know DAX and want to improve your skills, there is a 3-day Advanced DAX Workshop I will teach in London on May 13-15, 2013. It could be also a good reason to visit London if you come from other countries! Now that we are almost done with the PowerPivot for Excel 2013 book, we will write more articles – stay tuned!
|
-
DAX offers a set of Time Intelligence functions that simplify writing DAX expressions such as YTD, YOY and other time-related calculations. However, these functions only works when some assumptions are valid: your periods should be “natural” months and quarter. Some industries, such as retail and manufacturing, are used to accounting periods that are based on weeks instead of months. One month and one quarter are a set of weeks and a week cannot be split in different months, quarter or years. Making DAX working on these custom calendars requires you to write some DAX expression without using the built-in Time Intelligence functions. I wrote an article, Week-Based Time Intelligence in DAX, which describes how to write the common DAX calculations required on a custom calendar. I also included two samples, one for Excel 2010 and the other for Excel 2013, so that you can easily work on both versions (as you know, downgrading a workbook from Excel 2013 to Excel 2010 with PowerPivot is not possible). An important tip you will find is that creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. After all, this technique is very similar to the one you would use in SQL to perform the same calculation, for this reason DAX is considered more intuitive than MDX by developers with a SQL background.
|
-
Many people started using PowerPivot with Excel 2010. In order to start using PowerPivot for Excel 2010, you just have to download the add-in and install it for free. In Excel 2013, PowerPivot is already installed and you just have to enable it. However, you have to be careful about the Excel 2013 version you use, because not all the versions have all the features available. - Data Model features available in all Excel 2013 versions
- Internal xVelocity engine
- Load multiple tables in a data model
- Create relationships
- Navigate a data model with multiple tables using a single PivotTable
- Use only implicit measures
- PowerPivot features available in selected versions of Office 2013 (*)
- Create calculated columns
- Create calculated fields
- Use PowerPivot window and all the other features available there
- Also Power View is available only in these versions of Excel
So what are the version of Office 2013 that enable the usage of PowerPivot features? Here is the list: The only way to get these full BI features is through a Microsoft Volume License Agreement or Office 365 service. If you are not included in a Microsoft Volume License Agreement, the only way to get a copy of Excel 2013 that has all PowerPivot and Power View features available is getting an Office 365 ProPlus subscription. UPDATE Feb 27, 2013: read about a workaround to get a Volume License Agreement for just 30$. This might disappoint those of you that are used to buying a single license that never expires, but there is a good reason to move to Office 365 for using BI features: in the upcoming months and years, you will automatically receive updates of Office before perpetual (non-subscription) customers, and Excel will increase the number of BI features available at a faster cadence than ever before (yes, they promised it!). If you attended MS conferences and/or watched some of the last keynotes of BI speeches, you might have already seen some interesting previews (i.e. 3D mapping with GeoFlow for Excel), and probably more is coming. Again, it’s important to know that Office Standard 2013 does not include Business Intelligence features, so all the options available when you need fewer than five licences does not include PowerPivot and Power View. You have to get a subscription of Office 365 ProPlus in this case, and the only action you can to today is using the free preview of Office 365 ProPlus until the end of February, when such a subscription will be commercially available. I have seen some confusion in these first days of Office 2013 availability and for this reason I think it is important to clarify what is the right version of Excel you have to buy in order to use PowerPivot. I will update this blog post as soon as the Office 365 ProPlus will be commercially available.
|
-
A common question I receive is about how to correctly size a server for an Analysis Services Tabular instance. I always answer that an analytical evaluation is partly an empiric process, because there are many variables involved and the simplest approach is building the database model on a development server and then performing some workload test to understand how much memory and CPU is required. Another rule of thumb is to buy as much memory as you can. But this is not a simple approach if, for example, you want to optimize the size of a virtual machine (considerations about the opportunity to create a virtual machine with hundreds of GB of RAM are material for another post…). Microsoft published an interesting whitepaper: Hardware Sizing a Tabular Solution in SQL Server Analysis Services. This whitepaper explore in detail the operations required to estimate the required amount of RAM and CPU for a given tabular model. My suggestion is to read the whitepaper carefully, because buying hardware before the development of the solution could be a very bad idea. The best strategy is to allocate a budget at the beginning of the project and to delay the buying of the production server as soon as possible, so hopefully you will get more memory and faster CPUs at the same price. I’d like to quote a statement from the Hardware Configuration Examples that probably many people didn’t believe when pronounced by me at some conference – now that it’s on a Microsoft whitepaper I hope there will be more consensus on this! Anecdotally, we know that tabular models sometimes perform better on faster, newer processors than on high-end server hardware. Workstations that offer more in terms of raw processor performance are often first to market. When evaluating hardware, broaden your search to include workstations that you might not otherwise consider. The whitepaper has a lot of practical action to get real numbers and provides you a practical method to get a good estimate. There are also many links to documentation and articles, some of them from this blog and from SQLBI website.
|
-
Spring is a conferences’ season and the upcoming one is no exception. I will be speaking at PASS Business Analytics Conference 2013, which will be the first event this year, so I’d like to spend a few words about my sessions. PASS Business Analytics Conference 2013
April 10-12, 2013 | Chicago, IL – United States This conference is targeted to Business Analytics professionals. Thus, I expect to meet both BI Developers, Excel Advanced Users, Data Analyst and, of course, the new Data Scientist role (if you have a business card with such a definition, please drop me one, so I can demonstrate to skeptic people that this figure actually exists!). I have two sessions: - Modern Data Warehousing Strategy
- April 11th, 2013 – 1:30 pm – Chicago Ballroom VIII
Track: Strategy and Architecture - The recent introduction of new technologies such as PowerPivot, the BI Semantic Model, and columnstore indexes in SQL Server and advances in self-service business intelligence and big data might be considered threats to the classic data warehouse ecosystem. In reality, a good data warehouse is still the best starting point for any kind of analysis, but we do need to update our strategy for data warehouse implementation to fit the requirements of this new era. This session will start the conversation about what a modern strategy for data warehousing can and should be. What type of data modeling should we use for the data warehouse? What is the role of data marts? Does the use of technologies such as PowerPivot or Analysis Services Tabular affect the way we should model our data? Do columnstore indexes remove the need for an analytical server like Analysis Services? We will discuss these and other questions, offering an updated approach to the data warehouse modeling methodology.
- Self-Service Data Modeling
- April 12th, 2013 – 1:30 pm – Sheraton Ballroom I & II
Track: Data Analytics and Visualization -
Self-service business intelligence looks promising, empowering information workers to grab amazing insights from data. But are Excel 2013 and DAX language knowledge enough to analyze data? The answer in most cases is no – information workers will also need an ability to properly model their data and the skill to use some new tools to reshape data in the correct way. In this session, we will analyze some common problem scenarios where data analysis is difficult due to the shape of the model and see how to solve them. In theory, I expect two different audiences at the two sessions, but I know that there will be people attending both, especially who provides tools to end users. I’d like to receive feedback about what you would expect to see in such sessions (regardless you will attend or not!), so that I check if I defined the correct expectations for the audience. If you want to attend, register before March 15 in order to get a discounted price. You can also save $200 by using the code BAC228BL. See you in Chicago!
|
-
Are you working with SSAS Tabular? Are you an experienced PowerPivot user? In both cases, you should be aware that there is only one skill that is important for PowerPivot and SSAS Tabular, and it is the DAX language. I and Alberto have been using DAX since 2010, wrote several books containing several chapters about DAX and we know that there is still much to do. We have plans to publish more content online (more on this in a few months…) but we realized that the number of companies building tabular models is increasing every day. The common issues we see are about design, calculation, queries and performance. All of them are related to DAX, and we understand that learning DAX requires mentoring and practice (if only we had that 3 years ago…). Well, the good news is that now you can learn DAX deeper and faster. We created a new intensive DAX course that we called DAX Advanced Workshop. It is a three-day classroom that is aimed to Advanced PowerPivot users and Analysis Services developers that want to master the DAX language and improve their skills in performance optimization. The course includes hands-on lab sessions assisted by the trainer (me or Alberto), including exercises for creating queries, solving business problems and locating performance bottlenecks in DAX. Prerequisite: Attendees need to have a basic knowledge of the SQL 2012 Analysis Services Tabular modeling or they need to be familiar with PowerPivot for Excel and have produced at least some basic reports. A prerequisite of the course is the participation to a SSAS Tabular or PowerPivot Workshop, or having equivalent experience. If you think you’re ready for that, we have a single date in Europe before summer, and it will be in London on May 13-15, 2013. You can download course outline and register here. Seats are limited, hands-on-labs requires real assistance. You have to bring your laptop for hands-on-labs. It will be funny, but it will be tough! We don’t have plans for other editions until next fall, so if you are interested, free your agenda. Unless you want an on-site edition in another date, of course. Please, let me know if you are interested in US. You might have a good excuse to visit London, but if this is not enough, then give me your feedback. We will evaluate demand from US in order to schedule other public classes.
|
-
Spring is coming and we published many dates of SSAS Tabular Workshops around Europe and online. Alberto Ferrari is coming to Aarhus, Denmark, for a 2-day SSAS Tabular Workshop on March 12-13, 2013 (you can see more info and register here). And Alberto will also deliver a free session in a community event on March 12 evening: the session is PowerPivot for Excel 2013 in Action and you can find more info here. In the same month, I will go to Utrecht, The Netherlands, for the same 2-day SSAS Tabular Workshop on March 25-26, 2013 (info and registration here). We are working on a free community event on March 25, I will write an update post as soon as we define program and schedule. If you cannot travel, Alberto will also deliver a SSAS Tabular Workshop Online on February 27-28, 2013. The schedule is optimized for America’s time zones, so attending from Europe might be convenient if you are busy in daytime. You can register this week taking advantage of the discounted Early Bird price. And there is more to come… stay tuned!
|
-
The Microsoft has launched the new Office 2013, we are proud to announce the availability of the new PowerPivot Workshop for Excel 2013! We have a fresh new website that offers all the training options: online and classroom courses. The target of this training are Excel Advanced Users and there are two versions of the workshop: the BASIC version is 2-day long and is good for everyone who want to start his experience with PowerPivot; the FULL version is 3-day long and the additional day is dedicated to more DAX content and practice, so also the more advanced users can satisfy his needs. All these versions are based on our upcoming Microsoft Excel 2013: Building Data Models with PowerPivot book that will be available in March 2013 (workshop’s attendees receive a free copy of that book). The PowerPivot Workshop online is delivered every other month by me or Alberto and has different time scheduling depending on the delivery dates. We provide homework to students and use the available time online to teach content and answer attendees’ questions. The first online course will be delivered on February 19-21, 2013, and you have just one week to register using the use the Early Bird discount – more info for Basic and Full version following links. We established partnerships with several authorized training centers around the world to deliver PowerPivot Workshop in classroom: in this case hands-on-labs assisted by the teacher are part of the course. Moreover, the PowerPivot course can be delivered in local languages: we report date, city, state, country and language of the Workshop in the list of courses available on our web site. We will publish more dates and locations in the coming weeks. Please note we are looking for training centers in states and countries we still don’t cover – we already have many ongoing discussions, but if you are interested just write us to get more info. We'll also have more news for PowerPivot users in the coming weeks and months, which will be useful also to SSAS Tabular BI developers. The 2013 will be a long year!
|
-
Apparently Excel does not offer a way to import data in Excel by using a DAX query on Analysis Services. The Data Connection Wizard seems to offers only the ability to create a PivotTable when you connect to Tabular, but not a Table (see the Table option disabled in the next picture). 
However, the workaround is to create a connection file and changing it with an editor (such as Notepad) so that you can write your own DAX query (or MDX, too!) and import data in an Excel table. 
You can write a step-by-step guide on the article Import Data from Tabular Model in Excel Using a DAX Query I published on SQLBI web site.
|
-
Kasper de Jonge wrote a blog post last year introducing an interesting new feature in Excel 2013: you can write a DAX query that extracts data from the PowerPivot model and returns a table in Excel. Such a table can be used as a Linked table for the same PowerPivot model, creating an interesting opportunity to inject data in a data model mixing existing data and calculations obtained with Excel formulas. I wrote an article on SQLBI that describes this feature in more detail and we discuss this capability also in the book Excel 2013 Building Data Models with PowerPivot that will be available in March 2013 (you can already order it). Because data can flow back and forth between Excel tables and the PowerPivot data model, we created a particular definition for each type of “connected” table: - Linked Table (Excel –> PowerPivot): the classical Linked Table
- Reverse Linked Table (PowerPivot –> Excel): this is an Excel Table contained data resulting from a DAX query to the data model.
- Linkback Table (PowerPivot –> Excel –> PowerPivot): this is a Linked Table based on a Reverse Linked Table (usually integrating some new columns filled with constants and/or Excel formulas).
The article shows how to create a Linkback table step-by-step, applying different Excel calculations to different rows (something you cannot do on calculated columns in DAX), and it also contains some consideration about refresh order that is applied to Linkback tables.
|
|
|
|
|
|