Search results matching tag 'Methodology'

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

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!

SQLBits Training Day with SQLBI Methodology

sqlbi — Mon, 14 Feb 2011 10:20:00 GMT

You might already know that I and Alberto Ferrari will present the SQLBI Methodology in a Training Day at SQLBits 8. What you might need to know is that registrations are very good and there are not so many seats available. I don’t know whether it will be possible to get a larger room or not – so you have been advised, the sold out for this session might be a question of days!

SQLBI Methodology session recording available

sqlbi — Tue, 12 Oct 2010 15:21:00 GMT

If you lost the SQLBI Methodology session (or any other one) at 24 Hours of PASS in September, now you can watch the recordings on-line!

I just looked at the presentation and unfortunately there are 6 minutes missing from our presentation. Thus, if you are going to watch it, this is the missing initial slide that introduces the one-hour long discussion about how to build your Data Warehouse, Data Mart and OLAP Cubes using the SQLBI Methodology, which draft papers are available here.

Topics in SQLBI Methodology session

A methodology is far too complex for a simple session, nevertheless, we will make a simple introduction.

Draft papers can be found on
www.sqlbi.com/sqlbimethodology.aspx

–Brief description of Inmon and Kimball methodologies

–Our vision of the Data Warehouse

–New layers in the architecture

–The usage of views as an interface between layers

I will be at European PASS Conference 2009

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!

SQLBI Methodology at work

sqlbi — Wed, 01 Oct 2008 22:32:00 GMT

Today we published the draft of the paper "SQLBI Methodology at work". We applied the SQLBI Methodology to the well-known Adventure Works.

As usual, we look forward to get your feedack in the dedicated forum.

Methodology comparison: Kimball, Inmon and SQLBI

sqlbi — Sat, 20 Sep 2008 16:36:00 GMT

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

I am proud to announce a public draft of the first paper about the SQLBI Methodology. I and Alberto Ferrari tried to define a consistent methodology which covers the construction of the back-end of a BI Solution using Microsoft SQL Server and its complementary services. Now, we are looking for comments and feedback about it.

But, wait. Is this “yet another data warehouse methodology”? It depends from your point of view, because it strongly inherits concepts from both Kimball’s and Inmon’s methodologies, but it also includes concepts that are very specific to serve a multidimensional database like Analysis Services. However, before you start to read the whole paper, we start to make a fast comparison in this post between different methodologies we are talking about.

On the web, there are plenty of discussions regarding the Inmon vs Kimball debate. Both authors have fans that seem to believe that the choice between the two approaches to a data warehouse project is much more a religious war than a technical decision.

We (I’m writing these posts with Alberto Ferrari) do not want to start another war with this post, mainly because we do not believe that such a war has any reason of being, as is the case with any religious war.

Both Inmon and Kimball developed methodologies that work well in different kind of data warehouses. However, there are situations where the Kimball approach brings a fast and effective data warehouse and there are others where the Inmon approach leads to a cleaner solution. Both methodologies have drawbacks too and this post is not the right place to list all of them.

What is really important, in our opinion, is something else. If you choose the wrong approach, you will discover (usually too late) that you cannot complete your data warehouse project on time and on budget. Moreover, since the requirements in the BI world change frequently, it is often the case that you have to choose the methodology when the real requirements are still not clear.

Having worked with both methodologies for different projects, we believe that a careful analyst should take a different approach other then the “religious war”. For example, it would be a good idea to use the best of both methodologies, starting with the easier Kimball method but being ready to introduce Inmon structure if and when needed.

Our experience is that a clean analysis of the entire BI solution (including the data warehouse) can produce a very effective database and a set of ETL procedures that will let you get the best from both methodologies. Most important, this can change the general approach. The ability to change idea very late in the data warehouse building is very important, because it will let you follow the user requirements even when they deeply change.

If all the ETL process is designed with a strong and clean architecture, then it is possible to switch from Kimball to Inmon, in a smooth and easy way. Clearly, this is feasible if and only if you start – from the beginning – with a mixed approach in mind. This mixed approach is the one that is described in the first paper about the SqlBI methodology, downloadable here.

As said at the beginning, since this is the first public draft of the paper, we will be glad to receive feedback about it. You can contact us directly and you can also participate in the dedicate forum SQLBI Methodology forum.

Is a BI methodology truly independent from the underlying technology?

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.

Classification of BI solutions

sqlbi — Fri, 05 Sep 2008 07:54:20 GMT

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

One interesting question is “How do you measure the complexity of a BI solution?” If we need to decide whether a specific technique is suitable for a solution or not, it is very important to be able to classify that solution.

We want to define a classification of solutions based on their size and complexity. It is clear that size leads to complexity: even a simple solution made up of a few dimensions will become complex if it will handle several billions rows in the fact table.

Because the processing of data warehouses normally happens during the night, we propose a classification of BI solutions based on the time required for both the ETL execution and the cubes process to finish.

Small BI solution

We can rebuild a small BI solution each time because the entire ETL phase will consume a few hours of computation and does not need to keep complex history of changes in the dimensions.
If you are developing a small BI solution, you normally will recreate the whole relational database each time the ETL phase starts, producing a fresh database each night. This simple method of creating the data warehouse is very convenient, when applicable, because it highly reduces the complexity of handling the database. We can apply any change without worrying about the old data, just because there is no old data (where old data means data already processed and imported in the BI solution).
Even if this approach might seem strange, we find that many small or medium size companies have data warehouses whose elaboration will last no more than six, seven hours of computation. Using this pattern leads to simple solutions that are very easy to handle and with a very high return of investment.
We like to call a Small BI solution as a “one shot solution”, because it can be built with “one shot” of ETL computation.

Medium BI solutions

If we need to trace history of changes in the dimensions (i.e. we have some SCDs in the solution), the one shot solution seems to be no more a viable way. This is partially true.
If the complete solution can be rebuilt nighttime, we will always try to keep it as a one shot solution, computing it each night. If, for some reason, we need to maintain the history of changes of some specific dimensions, we think that it is easier to store the different versions of members of those dimensions in a persistent database, reloading all the facts and dimensions each night.
Doing this, we maintain the advantages of the one shot solution adding a slight complexity for the storage of different versions of the members of changing dimensions. Nevertheless, we still have full control on any database change, because we can rebuild the database during one pass of computation.

Large BI solutions

When the size of the fact tables becomes so big that the “one shot solution” is not a viable one, then the only choice is that of loading it incrementally each night, applying changes to the dimensions and adding new facts to it.
When a solution is very large, the overall complexity raises of some levels. We cannot rebuild the database easily, nor add attributes smoothly to the solution. Any change will require a high level of attention, because all operations will consume CPU and disk resources to a high degree.

Our experience is that the majority of solutions in the market are made of small or medium sized solutions. Whenever we start a new BI solution, we always need to understand in which scenario we are, because the architectural choices will highly depend on the size of the BI solution.

Methodologies to build BI Solutions

sqlbi — Fri, 05 Sep 2008 07:47:00 GMT

In the last years I spent most of my time working on BI solutions based on the SQL Server platform. I had experiences with other architectures but I mostly used Kimball's methodology to design relational databases feeding SSAS cubes. However, Kimball only describes the relational side of a BI solution and gives you a basic model (the star schema) that is the pillar of all the SSAS projects I made. The Kimball's model is product-agnostic and leveraging the modeling capabilities of SSAS requires you to make some decisions about how to model data, where to put some definitions and calculations and so on.

I and Alberto Ferrari worked together on several projects and we realized that today we use a methodology that is the result of years of experience and selection. We want to share our knowledge looking for flaws, comments and suggestions. I will start with some post with "Methodology" tag and very soon we will publish some whitepapers on SQLBI site. Please feel free to contact me for any feedback!