SQLBI - Marco Russo : Business Intelligence

First spring conference: PASS Business Analytics Conference and SQL Bits #passbac #sqlbits #sqlpass

Marco Russo (SQLBI) — Fri, 08 Feb 2013 15:50:00 GMT

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!

Parallelize incremental processing in Tabular #ssas #tabular

Marco Russo (SQLBI) — Tue, 11 Sep 2012 12:30:00 GMT

I recently came in a problem trying to improve the parallelism of Tabular processing. As you know, multiple tables can be processed in parallel, whereas the processing of several partitions within the same table cannot be parallelized. When you perform an incremental update by adding only new rows to existing table, what you really do is adding rows to a partition, so adding rows to many tables means adding rows to several partitions. The particular condition you have in this case is that every partition in which you add rows belongs to a different table.

Adding rows implies using the ProcessAdd command; its QueryBinding parameter specifies a SQL syntax to read new rows, otherwise the original query specified for the partition will be used, and it could generate duplicated data if you don’t have a dynamic behavior on the SQL side. If you create the required XMLA code manually, you will find that the QueryBinding node that should be part of the ProcessAdd command has to be moved out from ProcessAdd in case you are using a Batch command with more than one Process command (which is the reason why you want to use a single batch: run multiple process operations in parallel!).

If you use AMO (Analysis Management Objects) you will find that this combination is not supported, even if you don’t have a syntax error compiling the code, but you might obtain this error at execution time:

The syntax for the 'Process' command is incorrect. The 'Bindings' keyword cannot appear under a 'Process' command if the 'Process' command is a part of a 'Batch' command and there are more than one 'Process' commands in the 'Batch' or the 'Batch' command contains any out of line related information. In this case, the 'Bindings' keyword should be a part of the 'Batch' command only.

If this is happening to you, the best solution I’ve found is manipulating the XMLA code generated by AMO moving the Binding nodes in the right place. A more detailed description of the issue and the code required to send a correct XMLA batch to Analysis Services is available in my article Parallelize ProcessAdd with AMO. By the way, the same technique (and code) can be used also if you have the same problem in a Multidimensional model.

New licensing for SQL Server 2012 and #BISM #Tabular usage

Marco Russo (SQLBI) — Sun, 06 Nov 2011 09:11:18 GMT

Last week Microsoft announced a new licensing schema for SQL Server 2012. If you are interested in an extensive discussion of the new licensing scheme, Denny Cherry wrote a great blog post about that. I’d like to comment about the new BI Edition license....(read more)

Microsoft updates its BI Roadmap - #ssas #bism #teched #powerpivot

Marco Russo (SQLBI) — Wed, 18 May 2011 14:56:00 GMT

During TechEd 2011, Microsoft announced an important update to its BI roadmap . The reason why this is important is related to the previous announcement, which I discussed in November 2010 by including links to several sources and comments. At PASS Summit...(read more)

The Microsoft BI Roadmap: BISM, UDM and Beyond

Marco Russo (SQLBI) — Mon, 15 Nov 2010 13:05:00 GMT

Microsoft recently announced a new roadmap for its BI architecture. The next version of SQL Server, codenamed “Denali”, is going to introduce a new semantic model named BISM (Business Intelligence Semantic Model). Analysis Services will host it and it...(read more)

I will be at European PASS Conference 2009

Marco Russo (SQLBI) — Wed, 15 Apr 2009 15:33:00 GMT

Even this year I will be a speaker at the European PASS Conference 2009 (April 22-24, 2009 - Neuss, Germany).

Last year I had a session discussing the Many-to-Many Revolution paper.
This year I'm excited to introduce the SQLBI Methodology joining Alberto Ferrari on the stage. We got a lot of feedback until now, but this is the first time we will have the opportunity to discuss it in front of a broader audience (we made the same presentation some weeks ago at the Italian Microsoft SQL Server & Business Intelligence Conference 2009).

If you will attend the conference, stop us and say hello! It's always nice giving a face to a name. See you in Germany!

Self-Service Business Intelligence

Marco Russo (SQLBI) — Mon, 22 Sep 2008 20:31:00 GMT

The next Microsoft Business Intelligence Conference is coming and I just looked at sessions to include in my agenda. Every time I do this work, I find something in sessions descriptions that anticipates something that is coming out as news announcement in some of the keynote. But this time, session titles are enough to see that big news are coming.

CL211 New Horizons for Microsoft Business Intelligence with Self-Service Analysis Technologies
presented by Donald Farmer and Amir Netz

PL308 New Horizons for Microsoft Business Intelligence with Self-Service Reporting
presented by Lukasz Pawlowski, Carolyn Chau, Sean Boon, Roger Sanborn, Chris Baldwin, Thierry D'hers

I don't know if there are new products or new releases of existing products involved in these announcements. However, the keyword here is "self-service BI". Other vendors already used this term in the past, now it's the Microsoft turn. I hope to see real products and not only buzzwords.

BTW: I will attend the conference in Seattle - drop me a line if you will be there and you'd like to discuss the SQLBI Methodology with us (Alberto is coming too). You can find post about it using the Methodology tag.

Is a BI methodology truly independent from the underlying technology?

Marco Russo (SQLBI) — Sun, 14 Sep 2008 09:29:00 GMT

This post is part of a Methodology discussion - other posts will follows. I will be happy to get your feedback!

Building a BI solution (like any kind of software project), it is normal to look for existing methodologies and best practices, just to avoid pitfalls and get better results. Usually, a methodology is not related to a particular technology and/or software product. However, considering some details and/or features that will be available in your solution might affect important decisions that will be made. If we apply these concepts to a BI solution, we might discover that features of a particular product might have a wide impact in the overall architecture.

In this post, we want to consider the impact that the adoption of the SQL Server BI stack (and in particular of Analysis Services) can have on a methodology.

The first part of a modern BI solution is typically a data warehouse. Even if you adopt Analysis Services, usually you will have a relational star schema as a data source. This star schema will be part of a Data Mart extracted from the Data Warehouse, or sometime will be part of the Data Warehouse itself. If we talk about relational star schemas, and more in general if we talk about Data Warehouse modeling, there are well-known best practices to follow. Nevertheless, these papers and books are generally not tied to a particular platform; the only requirement is the use of a relational database. The model you will create will be not affected by the product of a particular vendor you will use as a DBMS. If the user is going to write its own queries against the Data Warehouse, everything is ok.

Now, when Analysis Services comes into the game, our previous choices might affect our final result. Analysis Services has several modeling capabilities that might change the relational Data Mart design we would have been thought otherwise. Just to name a few of these features:

Many-to-Many Dimension Relationships
Reference Dimensions
Parent-Child Dimensions
Join to dimension at different level of granularity
Data Source View (which allows the definition of views outside from the database)
MDX Scripts

At this point, several issues arise. Are the reference dimension a signal that snowflake schema might be better than star schema? Are the parent-child dimensions a way to avoid the construction of a fixed number of hierarchy levels corresponding to the maximum deep of the existing hierarchies? Should we prefer named queries in Data Source View instead of creating regular (and centralized) views into the relational database? Can the power of MDX Scripts substitute some part of the ETL processing?

Answering yes or not to the each of the previous questions might affect our architecture, and in particular the relational design (but also ETL). We didn’t mention the many-to-many relationships. When used in a simple way, they reflect the existing many-to-many dimension relationships existing in a regular Kimball design. However, as shown in the “The Many-to-Many Revolution” paper, we might create very atypical relational schema, just to satisfy our multidimensional modeling needs.

Thus, we need to consider how these changes affect our methodology of choice. If we want to leverage on features of a particular product, we will need to make several exceptions to a standard methodology. Not having a guide for these exceptions often brings to inconsistent results, where several people of the team (or, sometime, the same people over time) use different techniques to implement something that is not well described in the original methodology.

Moreover, if you extend these considerations to the client used to navigate OLAP cubes, there are more substantial differences. These differences impact both the user experience in terms of speed and ease of use and the format of the query sent to the server, forcing you to adapt the cube structure to the specific client.

For these reasons, in the last years we defined a set of rules, patterns and best practices that forms a specific methodology to implement a BI solution with the Microsoft SQL Server BI stack of services. We named it “SQLBI Methodology” and we will publish within September 2008 on the SQLBI web site.

Microsoft Business Intelligence Conference 2007 website live

Marco Russo (SQLBI) — Sun, 14 Jan 2007 08:41:00 GMT

The Microsoft Business Intelligence Conference 2007 website is now live. The conference is scheduled on May 9-11, 2007, in Seattle, WA. It's not a "traditional" Microsoft technical conference like TechEd and PDC: it's targeted more on project managers, decision makers, IT manager... and, yes, also IT professionals, but this indicates to me that only a part of the conference schedule will be dedicated to the (inside) technical side of Microsoft BI Products.

There is call for presentations dedicated to customer success story (I have a few possible proposals, but I will ask them before) and a section for customer awards nominations.

At this time there are no details on session topics, but only a track page containing only track names.

Strangely to me, Microsoft has (still?) not considered to give visibility to user communities, and I think this is not a good decision. In the last years Microsoft has made big investments in an effort to be more open toward their customers. "User communities" (with this term I mean peer-to-peer activities in general, ranging from blogs, forums, user groups, actions and sites that share the knowledge in general) in the BI market are still a rarity and probably only Microsoft has a minimum number of them.

This is not to say that I would like to be invited, but only that a BI professional does not have a reference for an "event to attend" where he can meet his peers. PDC is too much developer oriented, TechEd is too much general purpose, a SQL Conference is too much backend-oriented (we also need the client side, thus an integration with Office is necessary). The name "Microsoft Business Intelligence Conference" would seem perfect for this. I hope Microsoft will not lose the opportunity to leverage on existing peer networks.