Posts

Atlanta MS BI Group Meeting on Sep 29th

Join us on Monday, September 29th for our meeting of Atlanta MS BI Group to learn about DAX – the programming language of Power Pivot and Tabular.

Presentation:DAX 101
 Level: Beginner
Date:Monday, September 29th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:In this session, we will look at the new BI query language for PowerPivot and Tabular – DAX. This session will give you an introduction to PowerPivot and Data Analysis eXpressions (DAX) and will provide you the necessary understanding to start using DAX in your environment for data analysis. No prior experience with DAX is needed to attend this session.
Speaker:Damu Venkatesan is a Business Intelligence Consultant with over 25 years of IT experience. His experience includes architecting solutions in Business Intelligence using SQL Server, SharePoint & data management. He is the founder of Healthcare SQL Virtual Chapter for PASS. He has successfully delivered several BI/DW solutions and data migrations to various clients in Healthcare, Financials and Government applications using Microsoft technologies. He is on the board of DAMA Georgia chapter.
Sponsor:TEK Systems

Atlanta MS BI Group Meeting on Aug 25th

Join us tonight for a knowledge-packed meeting of Atlanta MS BI Group to learn how to optimize and automate your ETL.

Presentation:Making your SSIS Fly Beyond the Sky
 Level: Intermediate to Advanced
Date:Monday, August 25th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:How fast can an ETL package be? How big of a dataset is too big causing us to wait several hours to load data? Do you know those properties that you never touched because you were afraid to break something else? Well, let’s try them. In this demo-rich presentation, we’ll focus on some 101 tips that will maximize your package throughput. We will also go deeper toward a 200-300 level, when we will walk through some very interesting database design patterns that will ultimately affect the performance of your SSIS.
Speaker:Carlos Rodrigues has more than 15 years of experience, and most of it with Business Intelligence. Adept in implementing state-of-art Business intelligence and Data warehouse solutions using the entire Microsoft BI toolset, he currently manages onsite, offshore and remote teams but he remains technical and hands-on, and up to date with new technologies. Carlos delivered a few presentations for the Atlanta BI Group and Atlanta SQL Saturday. He follows Ralph Kimball’s school of thought.
Sponsor:Halo BI is dedicated to the simple principle that data is dumb; it’s people who provide the intelligence. No software package, no matter how fast it collects, processes or displays data, will ever deliver “business intelligence”. We are about developing technology to enable real business intelligence — the human kind.

Atlanta MS BI Group Meeting on Jun 30th

Join us for our next Atlanta MS BI Group meeting tomorrow, Monday, June 30th, to learn about implementing BI for financial reporting. You’ll also get a glimpse of the newly announced Microsoft Azure Machine Learning. Spread the word and invite your coworkers but RSVP on our website so we can plan food accordingly.

Main Presentation:Microsoft BI for Financial Reporting
 Level: Intermediate
Date:Monday, June 30th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:Using a Finance data mart in SQL Server 2012, I will demo how to use SSAS 2012 and Excel 2013 for Financial Reporting. This will include the basic DW model for Financial Reporting, how to configure the key dimensions in SSAS, how to setup writeback functionality in the Finance Cube with Security, and how to use Excel for the Reporting and writeback execution to update Budgets within the Cube.
Speaker:Justin Stephens began working with SQL Server 2000 (DW, DTS, and SSAS) in the fall of 2000 for a small software company in Texas. Early on, he became the SME for MDX Reporting using ASP and later ASP.NET. He was given a few projects using DTS/SSIS that expanded the reporting capabilities. In 2005, Justin spread my wings and began a long exodus from Texas that led him to Atlanta. Over the last 13 years, he has worked for/with some notable companies (Wal-Mart, McKesson, Dell, AMD, BCD Travel, Barclay’s) in various industries (Retail, Wholesale Distribution, Finance, Travel, and Education). During this time, he achieved MCITP in Business Intelligence using the 2008 R2 BI Stack. Justin is a die-hard proponent of the MSBI stack.
Sponsor:At TEKsystems, we understand people. Every year we deploy over 80,000 IT professionals at 6,000 client sites across North America, Europe and Asia. Our deep insights into IT human capital management enable us to help our clients achieve their business goals – while optimizing their IT workforce strategies. We provide IT staffing solutions, IT talent management expertise and IT services to help our clients plan, build and run their critical business initiatives.
Prototypes with PizzaMicrosoft Azure Machine Learning by Teo Lachev
With machine learning, computers can approach human performance in perception and understanding across vast amounts of data. Expensive and disconnected tools stood in the way of this innovation, but today Microsoft is democratizing machine learning. Get a glimpse of the Microsoft new cloud-based predictive analytics offering.

Atlanta MS BI Group Meeting on May 19th

Join us for our next Atlanta MS BI Group meeting tomorrow, Monday, May 19th, to learn about Power Query. Spread the word and invite your coworkers but RSVP on our website so we can plan food accordingly.

Main Presentation:Power Query
 Level: Intermediate
Date:Monday, May 19th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:So much data is stored on the web, yet merging it with our enterprise data can be a challenge. In this session we’ll use Power Query to quickly harvest historical data about SQL Saturday from its site, combining event, session and speaker details with other publicly available data sets. We’ll create custom functions with Power Query’s M language to collect and transform the data from multiple web pages. We’ll then move the data into Power Pivot and explore our results with Power View and Power Map. We’ll even cleanse the data without leaving Excel, leveraging DQS (Data Quality Services) through the MDS (Master Data Services) Excel add-in.
Speaker:Julie Smith is a Microsoft SQL Server MVP and a BI consultant for Innovative Architects in Atlanta, GA. She’s implemented increasingly complex ETL solutions for over a decade at various organizations. She’s also worn many other database “hats” in her career – production DBA and database developer–but ETL is her true passion. Julie is an MCTS in SQL Server 2008 BI and holds a BA from the University of South Carolina. She and Audrey Hammonds co-created The Datachix.com blog.

Rob Volk is a SQL Server DBA in the Metro Atlanta area since 2001. An MCITP, he also moderates and answers the forums on SQLTeam.com. While an old-time cranky DBA, he no longer considers “business intelligence” an oxymoron and is delightedly dipping his toes into the BI ocean, and loves to do things in new and unusual ways.

Sponsor:Since 2005, Innovative Architects has been serving industries to help solve business and technology challenges. Led by our great associates, we specialize in a wide array of cutting edge Microsoft technologies that we leverage to help our customers achieve the highest level of success with their technology solutions. We provide business consulting, development and implementation services for Microsoft platforms which include BizTalk, SharePoint, .NET, Business Intelligence and much more.
Prototypes with PizzaPower BI Forecasting by Teo Lachev

Building a Custom ETL Framework with SSIS 2012

Even though the SSIS catalog and the new project deployment mode in SQL Server 2012 take care of many of the mundane monitoring needs, a custom ETL framework is still important for handling features such as restartability and parallelism. Join our next Atlanta BI User Group meeting on Monday, March 31st when Aneel Ismaily will talk about this subject in his “Building a Custom ETL Framework with SSIS 2012” presentation.

“ETL Frameworks are the foundation of any data warehouse/data mart implementation. In this session we will discuss building a heavy-duty enterprise ETL Framework and will talk in detail about some major ETL enhancements available in SQL Server 2012. Topics include auditing, logging, designing for fault tolerance and recoverability, handling orchestration, parallelism and finally scheduling.”

X-IO will sponsor the meeting and present their Intelligent Storage Element fast storage product.

Atlanta MS BI Group Meeting on February 24th

Atlanta BI fans,

Join us for the next Atlanta MS BI Group meeting on Monday, February 24th. Lance England will present “introduction to MDX”. Computer Associates will sponsor our meeting and show us the latest Erwin release. I’ll also talk briefly and present Power BI now that it went live. Spread the word and invite your coworkers but RSVP on our website so we can plan food accordingly. Also, don’t forget to register for the SQL Saturday precons that will take place on May 2nd, the day before SQL Saturday Atlanta.

Main Presentation:MDX Fundamentals
 Level: Intermediate
Date:Monday, February 24th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:MDX is a powerful language, but the price of that power is a higher degree of complexity. The key to understanding MDX is to learn the fundamental concepts and have a mental model of how it works. This presentation is for anyone who has struggled with the MDX learning curve, or is interested in starting down the MDX path.
Speaker:Lance England is an Atlanta-area data professional specializing in data warehouse and business intelligence with a Microsoft-focus. He welcomes learning and networking with others. His blog is at http://lance-england.com and Twitter-handle is @lanceengland.
Sponsor:CA Technologies

CA Technologies’ portfolio of software and services helps our customers speed time to value, ensure critical application performance and simplify security and operations so they can drive innovation and quality for their customers. CA ERwin Data Modeler is an industry-leading data modeling solution that provides a simple, visual interface to manage your complex data environment both on premises and cloud.

Divorce Your Methodology

At the Atlanta BI meeting last night, there was a question from the audience about differences between Inmon and Kimball and which methodology should be followed when implementing a data warehouse. I’ll recapture my thoughts and the feedback I shared.

As BI practitioners, most of us use methodologies and it’s easy to fall in love with a specific methodology. But sometimes methodologies conflict each other. So, don’t feel very strongly about methodologies. Instead, study them and try to synthetize their best. The best methodology is the one that delivers a business solution in the most practical and simple way. Back to Inmon vs Kimball, I have deep respect for both of them. They both contributed a lot to data warehousing that forms the backbone of modern BI. Both of these two methodologies aim to consolidate data and promote a single version of the truth. Both of them are “pure” database-focused and vendor-neutral methodologies for designing data structures. But they also differ in significant ways. This table summarizes the high level differences between the two methodologies.

InmonKimball
APPROACHTop-down
Data warehouse first, data marts later
Bottom-up
Data marts first, data warehouse later
SCHEMANormalized (3NF) schemaDenormalized (star) schema
HISTORYAll history needs to be capturedDepends on business requirements
Type 1 vs Type 2 dimensions

 

So, which one to follow? My answer is that there is a place for both. Consider the following BI architectural view.

012914_0241_DivorceYour1

Data comes from veriety of data sources. Some data, such as Products, Customers, Organizations, represents master data that should be ideally maintained in a separate repository, e.g. in Master Data Services. However, most of the source data is not master data and must be staged before it’s imported in the data warehouse. Instead of a transient staging database whose data is truncated with each ETL run, consider an ODS-style staging database that maintains historical changes.

Start_DateEnd_DateStoreProductDeleted_Flag
1/1/20105/1/2010AtlantaMountain Bike 1
5/2/20103/8/2012AtlantaMountain Bike 2
3/9/201212/31/9999NorcrossMountain Bike 2

The Start_Date and End_Date columns are used to record the lifespan of each record and create row new versions each time the source row is changed. The example shows three changes that a given product has undergone. This design offers two main benefits:

1.    It maintains a history of all changes that were made to all columns to all tables. Typically, OLTP systems don’t keep track of changes so the staging database can be used to record changes.

2.    It maintains a full backup of the data. If a data warehouse needs to be reloaded, its history can be recreated from the staging database.

This ODS design is effectively your Inmon methodology in practice. For the data warehouse, I’d go with Kimball dimensional modelling. Dimensional modelling is a practical design technique whose goal is to produce a simple schema that is optimized for reporting. As far as data marts, I’m not so excited about moving data in or out of the data warehouse. In most cases, the most practical approach would be to implement a single data warehouse database and extend it as more subject areas come onboard. However, large organizations might benefit from data marts. For example, a large organization might have an enterprise data warehouse but for whatever reasons (usually IT not having enough resources), it might be difficult or not practical to extend it with a new subject areas. Then, this department might spin off its own data mart, such as on a separate database server (even from a different vendor, e.g. DW on Oracle and DM on SQL Server).

Ideally, the data mart should be able to reuse some of conformant dimensions from the data warehouse instead of implementing them anew. In reality, though, the enterprise bus could remain a wishful thinking. Having been left on his own devices, that department would probably need to implement the dimensions from the data they work with. For example, if this is an HR data mart, they would probably source an Organization dimension from PeopleSoft which is where their core data might come from.

With the risk of repeating myself, I want to reemphasize the role of the semantic layer which plays a critical role in every BI architecture. If you are successful implementing an enterprise bus consisting of a data warehouse and data marts (hub and spokes architecture), the semantic layer can provide a unified view the combines these data structures. For more information about the semantic layer benefits, refer to my newsletter “Why Semantic Layer“.

"Bus Matrix – the Foundation of your Dimensional Data Model" Atlanta BI Presentation

Our next Atlanta Microsoft BI meeting will be on Monday, January 27th. The main presentation is “Bus Matrix – the Foundation of your Dimensional Data Model” the speaker is Bill Anton. The meeting will be sponsored by TEK Systems. I hope you can make it.

The Bus Matrix is the cornerstone of a successful Dimensional Data Modeling strategy. It serves many purposes: from communicating requirements, capabilities, and expectations with the business users down to the prioritization and delegation of tasks across the development team. Join me in this session and learn what a Bus Matrix is, why it is the single most important document in your Data Warehouse project, and what can go wrong without it. We’ll also cover several approaches for creating and maintaining the Bus Matrix.

Bill Anton is an independent consultant whose primary focus is designing and developing Data Warehouses and Business Intelligence solutions using the Microsoft BI stack. When he’s not working with clients to solve their data-related challenges, he can usually be found answering questions on the MSDN forums, attending PASS meetings, or writing blog posts over at byoBI.com.

Atlanta BI Group Meeting on Monday

The Atlanta Microsoft BI Group will have a meeting tomorrow, June 24th.

Main Presentation: Developing a Custom Task in SSIS 2012 Level: Intermediate

Date: June 24th Time 6:30 – 8:30 PM ET

Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346

Overview: Integration Services uses tasks to perform units of work in support of the extraction, transformation, and loading of data. Integration Services includes a variety of tasks that perform the most frequently used actions, from executing an SQL statement to downloading a file from an FTP site. If the included tasks and supported actions do not completely meet your requirements, you can create a custom task. In this session we will demonstrate to you how to create custom SSIS tasks.

Speaker: Aneel Ismaily was born and raised in Karachi, Pakistan. He moved to the United States at the age of 18. Since then he has lived in Atlanta, GA. Aneel did his undergrad in Computer Science (BS) from Georgia State University (GSU) with concentration in Database Systems. He recently graduated with a professional MBA degree from Georgia State University with concentration in Organization Management and Entrepreneurship. Aneel owns MSBI Consulting, an IT consulting firm. MSBI Consulting provides Business Intelligence solutions to its customers. Prior to MSBI Consulting, Aneel was employed with Intellinet where he was working as a Principal Consultant. Before that he worked at RDA Corporation where he was working as a Sr. Software Engineer and before RDA he worked as a BI Solution Developer at BCD Travel. You can learn more about Aneel at http://www.linkedin.com/in/aismaily.

Sponsor: 3Sage Consulting Founded and led by real consultants who really care about the end deliverable, 3sage is untangling some of the most complex data issues in business today.

Prototypes with Pizza: Real-time BI with Big Data Demo by Teo Lachev

So, you have classic BI, self-service BI, Big Data BI, predictive BI, but do you have real-time BI? To demonstrate how classic BI, Big Data, and real-time BI can play together, Microsoft put together a great sample – Big Data Twitter Demo.

Record Attendance for Atlanta BI Group Last Night

We had the pleasure to have some 70 people attending our January 30th, 2012 meeting of the Atlanta BI group. Our sponsor, Matrix Resources, was kind enough to give us the auditorium. FisionIO sponsored the meeting. Phil Per-Lee did us a “Prototypes with Pizza” presentation, titled Connecting the Dots. And, Carlos Rodrigues rocked the stage with the main presentation about dimensional modeling.

I’ve uploaded pictures to the Photo Gallery section of our website and the slides to the Resources section. We’ve got some cool presentations lined up for next few months. Check our Calendar section to see what’s coming.