Search results matching tag 'BISM'

Campaign for #PowerPivot and #Tabular textual modeling language #msbi #ssas

sqlbi — Mon, 03 Jun 2013 22:14:00 GMT

If you think PowerPivot and Tabular are good tools but sometime the design tools are not fast/complete/flexible enough, please consider voting this suggestion on Connect to request a textual representation Tabular or PowerPivot Data Models.

This is the simplest way to improve the Productivity of PowerPivot and Tabular users/analysts/designers.

I would like having a simple textual representation of a BISM model, that can be translated back and forth to a XAML representation of the data model (this could be then integrated within Visual Studio, but other development tools might use this language as well).
I’d like to write (for Tabular) something like:

TABLE Customers (
    CustomerKey INTEGER,
    Name STRING,
    Weight FLOAT
)

TABLE Sales (
    CustomerKey INTEGER ref Customers[CustomerKey],
    Quantity INTEGER,
    Amount CURRENCY
)

This would be the syntax to keep the functional-style, even if I would prefer a more declarative language such as:

TABLE Customers
    CustomerKey INTEGER
    Name STRING
    Weight FLOAT
END TABLE

TABLE Sales
    CustomerKey INTEGER ref Customers[CustomerKey]
    Quantity INTEGER
    Amount CURRENCY
END TABLE

We can discuss about the syntax, but we all know that this can be done, especially for Tabular which has a smaller number of properties to handle if compared to Multidimensional (which could benefit from the same approach, too).
The implementation of tools enabling the translation of models to/from this language would be cheaper that writing/improving advanced graphical editors and many other features that you have for free once you have a text file:

Source control
Revision history
Version comparison
Team development

This is the single feature for Tabular/PowerPivot with the best ROI that I can imagine.
And if Microsoft does not time to implement it… well, at least they could publish a document such as “Tabular Language Draft 1.0” and somebody in the community will implement the necessary translation tools. Having an "official" Language is important, because in this way nobody would spend time on something that could change too much in the future.

Are you convinced? Nice!
So please, help me in this campaign for a PowerPivot and Tabular textual modeling language.
This is the call for action:

Vote this item on Connect
Spread the word as loud as you can!

Don't wait, do it now!

Workshop and Events in Europe next month #ssas #tabular #powerpivot

sqlbi — Thu, 21 Feb 2013 09:41:00 GMT

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)

Free community event where Marco Russo (yes, always me!) will show how to write and optimize queries in DAX
More info here and free registration here

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.

SQLBits XI in May 2013 – many reasons to attend! #sqlbits #dax #tabular

sqlbi — Wed, 20 Feb 2013 09:33:00 GMT

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!

xVelocity engines compared: VertiPaq vs ColumnStore #ssas #vertipaq #xvelocity #sql #tabular

sqlbi — Wed, 22 Aug 2012 11:56:00 GMT

During the last months I and Alberto worked in several projects using Analysis Services Tabular and we had to face real world issues, such as complex queries, large data volume, frequent data updates and so on. Sometime we faced the challenge of comparing Tabular performance with SQL Server. It seemed a non-sense, because even if the same core xVelocity technology is implemented in both products (SQL Server 2012 uses ColumnStore indexes, whereas Analysis Services 2012 uses VertiPaq), we initially assumed that the better optimization for the in-memory engine used by Analysis Services would have been always better than SQL Server.

However, we discovered several important things:

Processing time might be different and having data on SQL Server could make ColumnStore way faster for processing.
Partitioning in SQL Server might be much more effective for query performance than Analysis Services.
A single query can scale easily on more processor on SQL Server, whereas in Analysis Services the formula engine is single-threaded and could be a bottleneck for certain queries.
In case of a large workload with many concurrent users, storage engine cache in Analysis Services could be a big advantage over SQL Server, especially for scalability

As you can see, these considerations are not always obvious and you might be tempted to make other assumptions based on these information. Well, don’t do that. Before anything else, read the whitepaper VertiPaq vs ColumnStore Comparison written by Alberto Ferrari. Then, measure your workload. Finally, make some conclusion. But don’t make too many assumptions. You might be wrong, as we did at the beginning of this journey.

Finally in stock: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model #ssas #tabular

sqlbi — Thu, 26 Jul 2012 10:32:00 GMT

It has been a long wait, but finally our book about Analysis Services Tabular is available!

Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model

Amazon is already shipping it in US and it should be available soon also in Europe. However, the Kindle edition is already available everywhere! If you like to order it on Amazon, here are the links for all the shops:

Amazon.com: hardcopy – kindle
Amazon.ca: hardcopy
Amazon.co.uk: hardcopy – kindle
Amazon.de: hardcopy – kindle
Amazon.es: hardcopy – kindle
Amazon.fr: hardcopy – kindle
Amazon.it: hardcopy – kindle

You can follow this link to take a look at the Table of Contents, at the Introduction and some content of other chapters. I suggest you to read the introduction and the "Who Should Read This Book” section before buying the book, so you will be sure you are getting the right one. Well, if you read this blog, you should be in the right target audience, but double check is not bad.

For your convenience, this is the list of the chapters:

Chapter 1 Introducing the Tabular Model
Chapter 2 Getting Started with the Tabular Model
Chapter 3 Loading Data Inside Tabular
Chapter 4 DAX Basics
Chapter 5 Understanding Evaluation Context
Chapter 6 Querying Tabular
Chapter 7 DAX Advanced
Chapter 8 Understanding Time Intelligence in DAX
Chapter 9 Understanding xVelocity and DirectQuery
Chapter 10 Building Hierarchies
Chapter 11 Data Modeling in Tabular
Chapter 12 Using Advanced Tabular Relationships
Chapter 13 The Tabular Presentation Layer
Chapter 14 Tabular and PowerPivot
Chapter 15 Security
Chapter 16 Interfacing with Tabular
Chapter 17 Tabular Deployment
Chapter 18 Optimizations and Monitoring
Appendix A DAX Functions Reference

We already received a good review but I hope to see many others – of course we all like the good ones, but we always look carefully at every comment so that we can improve the next one!

I’d like to give a big thank you to all the people who helped us reaching this goal. I know we set an ambitious target, trying to cover a brand new engine and development environment in a very deep way despite its infancy on the market. I think that this is just the first step in this new world, Tabular will evolve and we’ll gain more experience working with it on real word projects. But I hope that many BI developers will find it easier to start a new Tabular project with the guidance of this book. We have found that Tabular is good not only for “simple” models, but also for the complex ones, when Multidimensional faced some limitations in term of leaf-level calculation. I still don’t use Tabular for any new project, there are still many scenarios in which Multidimensional is a better choice, but don’t wait too much to start playing with DAX and Tabular. You might miss some real opportunities.

I hope you will enjoy the reading!

Watch @marcorus and @ferrarialberto sessions online #teched #msteched #tee2012

sqlbi — Sat, 07 Jul 2012 05:41:30 GMT

In June I participated to two TechEd editions (North America and Europe). I and Alberto delivered a Pre Conference and two sessions about Tabular. Both conferences provides recorded sessions freely available on Channel 9 so that you can compare which one has been delivered in the best way! If you have to choose between the two versions, consider that in North America we receive more questions during and after the session (still recording), increasing the interaction, whereas in Europe questions usually comes after the session finished (so no recording available). If you’re curious, watch both and let me know which version you prefer, especially for Multidimensional vs Tabular!

BISM: Multidimensional vs. Tabular (TechEd North America 2012)
BISM: Multidimensional vs. Tabular (TechEd Europe 2012)
Many-to-Many Relationships in BISM Tabular (TechEd North America 2012)
Many-to-Many Relationships in BISM Tabular (TechEd Europe 2012)

If you are interested to learn SSAS Tabular, don’t miss the next SSAS Tabular Workshop online on September 3-4, 2012. We are also planning dates for another roadshow in Europe this fall and I’m happy to announce we’ll have two dates in Germany, too. More updates in the coming weeks.

Optimize Many-to-Many with SUMMARIZE and Other Techniques

sqlbi — Fri, 01 Jun 2012 12:12:00 GMT

We are still in the early days of DAX and even if I have been using it since 2 years ago, there is still a lot to learn on that.

One of the topics that historically interests me (and many of the readers here, probably) is the many-to-many relationships between dimensions in a dimensional data model. When I and Alberto wrote the The Many to Many Revolution 2.0 we discovered the SUMMARIZE based pattern very late in the whitepaper writing. It is very important for performance optimization and it should be always used. In the last month, Gerhard Brueckl also presented an approach based on cross table filtering behavior that simplify the syntax involved, even if it’s harder to explain how it works internally.

I published a short article titled Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering on SQLBI website just to provide a quick reference to the three patterns available. A further study is still required to compare performance between SUMMARIZE and Cross Table Filtering patterns. Up to now, I haven’t observed big differences between them, even if their execution plans might be not identical and this suggest me that depending on other conditions you might favor one over the other.

Order of Evaluation in CALCULATE and Clever Hierarchy Handling in #dax

sqlbi — Wed, 02 May 2012 04:36:00 GMT

After the first two editions of the SSAS Tabular Workshop, I and Alberto had some time to flush some of the pending activities. We published two articles:

Clever Hierarchy Handling in DAX: DAX is not a hierarchy-friendly language, especially if you have a MDX background. If you have to implement hierarchy-based calculation, MDX is still better. But if you need to do that in DAX (for example, because you need this in a DAX measure), then you have to read this article written by Alberto Ferrari that explains how to correctly calculate the classical “ratio to parent” pattern in several different scenarios.
Order of Evaluation in CALCULATE Parameters: a common mistake when you start working with DAX is understanding the evaluation order of CALCULATE parameters and what happens when you have nested CALCULATE statements. The topic can be very dense, so I tried to write a simple article that explain the basic concepts and based on the feedback I’ll receive, I’ll consider writing other articles on this subject. So feedback is welcome!

We have other ideas and material that I hope we’ll be able to write and publish in the upcoming weeks. In the meantime, there are plenty of other editions of the workshop this month, starting from the online one next week that is arranged for US time zone. Don’t miss it!

Last minute update #ssas #tabular community event in Amsterdam on April 16

sqlbi — Fri, 13 Apr 2012 23:45:00 GMT

We finally have the registration website for the community event about Tabular in Analysis Services in Amsterdam on April 16 evening that I introduced in my previous post - don't miss it, the registration are open (and limited!) at:

http://passnl-april2012.eventbrite.com/

This is really a "last-minute" event, but we're pretty confident the room will be full anyway! See you there!

#SSAS #Tabular Workshop and Community Events in Netherlands and Denmark

sqlbi — Thu, 12 Apr 2012 11:04:00 GMT

Next week I will finally start the roadshow of the SSAS Tabular Workshop, a 2-day seminar about the new BISM Tabular model for Analysis Services that has been introduced in SQL Server 2012. During these roadshows, we always try to arrange some speeches at local community events in the evening - we already defined for Copenhagen, we have some logistic issue in Amsterdam that we're trying to solve. Here is the timetable:

Netherlands

SSAS Workshop in Amsterdam, NL – April 16-17, 2012

2-day seminar, I and Alberto will be the trainers for this event, register here

We're trying to manage a Community event but we still don't have a confirmation, stay tuned

UPDATE: the community event has been comfirmed, please register here: http://passnl-april2012.eventbrite.com/

Denmark

SSAS Workshop in Copenhagen, DK – April 26-27, 2012

2-day seminar, I and Alberto will be the trainers for this event, register here

Community event on April 26, 2012

This event will run in Hellerup, at Microsoft venue
All details available here: http://msbip.dk/events/26/msbip-mode-nr-5/
People from Sweden are welcome! Just register to this private group on LinkedIn in order to announce your presence, so we’ll know how many people will attend

In community events we’ll deliver two speeches – here are the descriptions:

Inside xVelocity (VertiPaq)
PowerPivot and BISM Tabular models in Analysis Services share a great columnar-based database engine called xVelocity in-memory analytics engine (VertiPaq). If you want to improve performance and optimize memory used, you have to understand some basic principles about how this engine works, how data is compressed, and how you can design a data model for better optimization. Prepare yourself to change your mind. xVelocity optimization techniques might seem counterintuitive and are absolutely different than OLAP and SQL ones!

Choosing between Tabular and Multidimensional
You have a new project and you have to make an important decision upfront. Should you use Tabular or Multidimensional? It is not easy to answer, because sometime there is a clear choice, but most of the times both decisions might be correct, at least at the beginning. In this session we’ll help you making an informed decision, correctly evaluating pros and cons of each one according to common scenarios, considering both short-term and long-term consequences of your choice.

I hope to meet many people in this first dates. We have many other events coming in May and June, including an online event (for US time zones), and you can also attend our PreCon Day at TechEd US in Orland (PRC06) or TechEd Europe in Amsterdam. I’ll be a good customer for airline companies in the next three months!

I’m just sorry that I hadn’t time to write other articles in the last month, but I’m accumulating material that I will need to write down during some flight – stay tuned…