• Post Categories

  • Browse Blogs

  • Blog Stats

    • 483,300 hits
  • Syndications

    SQLServerPedia Contributor

My PASS Summit 2013 Presentations

PASS_2013_SpeakingButton_180x180-1I had the honor to speak for the first time at this past PASS Summit 2013 in Charlotte, NC. It was a great opportunity for me as this is one of the most prestigious nationwide conferences on SQL Server. I delivered 3 different sessions on Business Intelligence related topics. Attendance to my sessions exceeded my expectations and my overall experience as a first-time speaker was very positive. I also need to add that the God’s of the live demos were on my side.

My session titled PowerPivot + Power View = Self-service BI Revolution was full and had about 5 to 8 people standing in the back. Everybody got excited when I did a demo of the nice interactivity in Power View and 3D geographical capabilities of PowerMap.

The Lightning Talk room was packed and people had to be turned away. This room should be bigger next time as these Lightning Talks are very popular. Everybody accepted and agreed with my postulation on the need for Conformed Stored Procedures in addition to Conformed Dimensions and Facts. After the Lightning Talks were over, several attendees came forward to discuss further how Conformed Stored Procedures should be implemented. One attendee in particular, mentioned that he had been thinking about proposing a similar approach to his team and this talk helped him get re-assurance of this approach.

My last session on SSAS Hierarhcies Tips & Tricks was a half-day session on the last day of the summit between 2:00pm and 5:00pm. It was half-full for the first half of the session, and ended up about 1/4 full.  This drop-off is expected towards the end of the Summit as many people fly back home on Friday and usually leave around 4:00pm.

I’m very pleased with the turnout and feedback received. The audience in all of my sessions was very engaged and had a lot of great questions. I definitely look forward to speak at PASS Summit 2014 in Seattle, WA and at PASS BA Conference 2014 in San Jose, CA.

To download my presentations slides click on the links below:

1. Regular Session (75 minutes): 4798 PowerPivot + Power View = Self-Service BI Revolution
2. Lightning Talk Session (10 minutes): Slides: 5511 Conformed Stored Procedures Script: ConformedStoredProcs_SQLScript
3. Half-Day Session (3 hours): 4803 Fast Performing SSAS Hierarchies Tips and Tricks

Thanks to all the attendees and PASS team for this great opportunity. I hope I met or exceeded everyone’s expectations.

If you attended one of my presentations, please leave your comments and feedback!

The affordable self-service BI revolution has started.

I am officially announcing that I have now become an independent consultant and business owner. Becoming independent has always been my dream and I have now decided to follow that dream. I believe it is the right time to do it for my family and myself. It’s time to soar!

Why did I leave Pragmatic Works?

Simple. I wanted to follow my career goals and carve my own path. It was a hard decision to leave Pragmatic Works as I really enjoyed the work environment and had great relationship with the top brass Brian Knight, Adam Jorgensen, and Sam Washburn as well as my co-workers. The folks at Pragmatic Works gave me a lot of opportunities to learn and grow for which I am forever grateful. It is a great company to work for and highly recommend working for them. I would return to work for them in a heartbeat.

What are my plans?

First of all, I have created a corporation, called Agile Bay, Inc. (http://www.agilebay.com) through which I will be providing Business Intelligence consulting services, Software Development, Training and Staffing services. I will also be doing contract work and hiring BI and Software developers to take on some of these jobs.

My plan is to focus on self-service Business Intelligence solutions using the Microsoft toolset, including:

  • SQL Server Database Services
  • Analysis Services
  • Reporting Services
  • Master Data Services
  • Data Quality Services
  • SharePoint
  • Performance Point
  • PowerPivot
  • PowerView
  • & more…

I will continue leading, volunteering and sharing my knowledge in the SQL Server and Business Intelligence community and in PASS.

Enter the affordable self-service BI revolution

The new release of SQL Server 2012 will bring a lot to offer in the BI space. I truly believe it will be a game changer and many companies will embark in new Data Warehouse, Business Intelligence and Master Data Management projects. Some of these projects may have been put on hold due to licensing and development costs. This is where SQL Server 2012 will shine as it introduces lower BI licensing costs with the new SQL Server Business Intelligence Edition. Organizations will no longer require Enterprise Edition to do all the cool things Microsoft has to offer with their BI suite.

Additionally, the shift to the BI Semantic Model and the focus on self-service BI will open a lot of doors to consulting firms with lower overhead like mine. Enter the affordable self-service BI revolution!

Hitting the ground running

I am very grateful to the fact that the word has spread out among my immediate professional network and have already been booked for projects and contract work several months ahead.

I have tried not to market myself or my company too much during this initial phase as I want to manage my growth more organically. I am in the process of hiring BI developers at all levels, so if you are interested feel free to contact me via http://www.agilebay.com/#!contact.

 Mission

My mission is to empower individuals and organizations through the Microsoft Business Intelligence Toolset.

Vision

My vision is to be the catalyst and leader of the affordable self-service Business Intelligence revolution.

Value proposition

There are many alternatives available when chosing a consulting firm to help you achieve a succesful Business Intelligence, Data Warehouse and Master Data Management implementation.  My value proposition is to achieve this same success at an affordable budget and by providing you with the knowledge transfer and mentoring needed to continue your own development efforts.

The sales pitch

 You need an experienced and expert professional to help you with your design and development efforts.  I have experience and expertise. Let’s talk.

You may contact me through my company’s website at http://www.agilebay.com/#!contact or through email: info@agilebay.com

I’m also available for any quick help through email at jchinchilla@sqljoe.com. Make sure to check out my blog at http://www.sqljoe.com as well.

Afterthoughts: IT Pro Camp Orlando 2012

This past Saturday January 21, 2012 I had the opportunity to speak at my first IT Pro Camp in Orlando, FL. This event was put together by Blain Barton ( Blog | LinkedIn | Twitter ), Microsoft Senior IT Pro Evangelist and Chad Miller ( Blog | LinkedIn | Twitter ), Microsfot MVP and Senior Manager of Database Administration at Raymond James Financial.

Event

The event went very smooth and had a good turnaout. Food was amazing. They reallly outdid themselves with good old southern BBQ.

Presentation

I presented a session titled Introduction to Microsoft Business Intelligence (Slides), focusing on the basic concepts of BI and Datawarehousing, the Microsoft toolset and my Top 5 Rules of Wisdom for succesful BI. I had a great turnaout, about 25 attendees. All of the attendees were very engaged and asked excellent questions. I enjoyed presenting to the Orlando crowd.

Picture credits: Russel Faustino. See more pictures of the event here.

Other IT Pro Camps are being added. Check the IT Pro Camp website here to keep up to date.

Click on the picture below to download my Intorduction to Business Intelligence powerpoint presentation.

 

 

SQL Saturday 86 BI Edition Tampa 2011: Afterthoughts

Wow! Just Wow! That’s all I can say to describe how great SQL Saturday 86 was on November 5th in Tampa, FL . I got so much positive feedback from every attendee, speaker and sponsor. Food was great, sessions were amazing, speakers were first class!  I have to say thanks to everyone for attending, speaking, sponsoring and helping out with these great event put together by the Tampa Bay Business Intelligence User Group officers and volunteers.

Organizing Committee & Volunteers

I cannot take personal credit for this event. It was truly a team effort. Somewhere I read that in order to be successful you need to surround yourself with talented & driven individuals that are committed to your same goals. Maybe I’m paraphrasing or simply making that up, but this has proven to work with running our local Tampa Bay Business Intelligence User Group and the SQL Saturday #86 BI Edition event.

The organizing committee was made up by:

The volunteers that participated in our event included:

  • Randy Borys
  • Gloria Salcedo
  • Chris Richardson
  • William Sanders
  • Pam Shaw
  • Richard Pyra
  • Wes Helton
  • Tom Totten
  • Denis Desault
  • David Greim
  • Lena Pavlyuk
  • Lyn Taylor

Speakers

Our speakers were world class speakers to say the least. The speaker roster included Microsoft MVPs, field engineers and evangelists and other professionals that committed their time and paid for their own travel to support our event. We had just a few speakers that cancelled for personal, health or work-related conflicts. We still want to express our appreciation to them for willing to support our event as well. Thankfully, we managed to secure alternate speakers who did a great job even though it was last minute. Thank your SQL Saturday #86 Speakers!

Sponsors

Our sponsors made our event a quality and epic event by sponsoring with cash, items and swag to giveaway. Thanks to them we were able to sponsor great Cuban food from LATAM restaurant, coffee and donuts throughout the day, swag and raffle prizes and provide our speakers with a thank you dinner.

  • KForce provided their facilities for our event.
  • Convergence Consulting Group (Twitter | Website) did an amazing job with our speaker and event organizers’ shirts.
  • Quest Software (Twitter | Website) raffled an iPad and a $100 Amazon gift card.
  • Redgate Software (Twitter | Website) offered a great variety of books to give away.
  • Pragmatic Works offered a free training voucher for any of their online classes.
  • LaSalle Computer Learning School offered a training voucher for any of their in-person SQL Server certification classes.
  • Fusion-IO raffled an iPad.
  • Idera raffled one their SQL Serve monitoring software suites and gift cards.
  • Soaring Eagle, ElSavier and Wrox each provided SQL Server and Business Intelligence books to raffle.

To all our sponsors thank you for supporting our SQL Server and Business Intelligence Community!

Panel of Experts

In this event I decided to put together a panel of experts to discuss several topics relating to SQL Server and Business Intelligence as well as a discussion of what’s new in the upcoming release of SQL Server 2012. The Panel of Experts took place at LATAM restaurant right after lunch. Our panel of experts included:

We invited our sponsors to briefly talk about their products and services and prizes they were giving away during the event. Following our sponsors I decided to recognize Andy Warren, MVP and PASS (Website) Board of Directors, for his contributions to the SQL Server community, SQL Saturday creation and 100 SQL Saturday milestone.

BI BootCamp pre-conference

Along with our free SQL Saturday #86 event we hosted a paid pre-conference titled “BI BootCamp” presented by SQL Server MVP and SSAS Maestro Instructor Adam Jorgensen. We exceeded our goal with 33 attendees paid attendees, all of which gave very positive feedback of Adam’s presentation. I did a star appearance, helping Adam present a section on SSRS Reports using OLAP cubes as a source.

Thanks to Mike Wells for (Twitter | LinkedIn | Blog) for taking pictures throughout the event. You can see more event pictures at:

https://picasaweb.google.com/116314811362928917852/SQLSat86

GiveCamp Tampa 2011

This past weekend I participated at GiveCamp Tampa 2011 (http://www.givecamptampabay.org/) as part as the Worldwide GiveCamp (http://www.givecamp.org) weekend sponsored by Microsoft and other partners. Per GiveCamp’s website:

GiveCamp is a weekend-long event where technology professionals from designers, developers and database administrators to marketers and web strategists donate their time to provide solutions for non-profit organizations. Since its inception in 2007, the GiveCamp program has provided benefits to hundreds of charities, worth millions of dollars of developer and designer time in services!

At GiveCamp Tampa 2011 we chose two non-profits to be the recipients of our development efforts. David Liebman and a group of developers, implemented a content management site for a school. I architected and developed a Data Warehouse and Business Intelligence solution along with 3 other developers for the Florida Children Services Counsel (FCSC) (http://www.floridacsc.org).

About Florida Children Services Council (FCSC)

The Florida Children’s Services Council (Florida CSC) is a non-profit association that represents children’s services councils in counties throughout the state. Florida CSC employs the collective strengths of these public organizations to improve young lives by making strategic investments in the well being of Florida’s children. Its mission is to promote policies that build effective primary prevention and early intervention systems for young children and their families

The GiveCamp Tampa 2011 Business Intelligence Team
(From left to right: Pam Shaw, Terry Brennan, Paul Drumm, Wes Helton, Jose Chinchilla)

Florida CSC Business Intelligence Solution

The scope of our work included the following tasks:

  1. Installation and Configuration of SharePoint 2010 and Performance Point
  2. Installation and Configuration of SQL Server 2008R2
  3. Architecture and Development of a Relational Data Warehouse
  4. Design and Development of ETL framework using SSIS Packages
  5. Design and Development of SSAs OLAP cubes
  6. Design and Development of SSRS Reports and Performance Point Dashboards

The dataset in scope for the FCSC BI solution included Budget Allocation and Participant Demographic data for the 8 different counties participating in the SAMIS Collaborative including Pinellas, Broward, Martin, Duval, Palm Beach, Miami-Dade, Hillsborough and St. Lucie.

Sample Performance Point dashboard developed:

Afterthoughts

It was overall a great experience being able to dedicate my time to the Florida CSC knowing that the BI solution delivered will serve as starting point to derive insightful analysis to solve the needs of children and families in the State of Florida. More than 90 hours of combined development time was dedicated to this solution. All the team members rocked!

I really enjoyed working with the GiveCamp Tampa 2011 team and look forward to the next GiveCamp event!

Download all SQL Server DENALI CTP3 Demo VHD 36 files at once

Microsoft has made available for download the SQL Server DENALI CTP3 Demo VHD several weeks ago at http://www.microsoft.com/download/en/details.aspx?id=27740. The SQL Server DENALI CTP3 Demo VHD is a fully configured HyperV image of SQL Server Denali CTP3  and SharePoint 2010 good for 180 days. The following software is configured on the virtual machine:

  • SQL Server “Denali” CTP3
  • SharePoint 2010
  • Office 2010

You may want to download this pre-built image to test and play with all the new features available in SQL Server Denali CTP3 and SharePoint 2010 without going through the hassle of building the environment yourself.

The vhd file has been broken down into 36 compressed (RAR) files of 700MB each. You can avoid the pain of downloading each file one by one using a download manager such as the Download Accelerator Plus (DAP).

DAP is my favorite download manager for 3 simple reasons:

  1. It’s free
  2. It’s fast
  3. The “Download all with DAP” option

For those of you not familiar with DAP, you can download it for free at http://www.speedbit.com. After downloading and installing DAP, you will notice that one of the options now available when you right-click on a webpage is the “Download all with DAP” as seen in Figure 1.

Figure 1. Download all with DAP option

By default, it will display a list of all downloadable items from the webpage. To filter the file types to download, click on the More button. An additional section displays at the bottom with a button to Filter. Click on the Filter button, and check Custom. Type RAR EXE with a single space in between the two words as shown in Figure 2.

Figure 2. Filter option

Click OK. Only the downloadable items with extension RAR and EXE are selected and ready for download. To start downloading click on the Download Now button. By default only 4 items will download in parallel, the rest will be queued and start downloading when one of the first 4 items complete as shown in Figure 3.

Figure 3. DAP queue

Using Unary Operators to control Analysis Services hierarchy aggregations

Analysis Services hierarchy aggregations can be easily controlled using unary operators. For example, in accounting there are GL accounts that are grouped in major GL account groups and used in different financial statements like Profit & Loss Statement, Income Statement, Trial Balance, and Balance Sheet. These GL accounts may affect the balances of the major GL account groups and  financial documents differently, adding  or subtracting to the balance.

A simpler example, might involve sales quotas for a Sales Department. For example, in some organizations the Sales Department is broken down into sales teams with salespeople assigned to those teams. The overall Sales Department quota is broken down into smaller quotas among these sales teams and the sales team’s quota is then broken down into quotas assigned to the team members. In some organizations, Sales Managers often assign these quotas evenly throughout the sales teams and sales team members. Others, in order to guarantee a fat bonus check decide to raise the bar and assign sales quotas that are higher to the overall department’s sales quota. Figure 1 below shows an organizational structure chart of our sample Sales Department with sales quotas that don’t sum up evenly.

Figure 1. Sales Department organizational structure chart and sales quotas

As can be seen in Figure 1, the total Sales Department quota is $150,000. The Sales Manager in this case decided to push their teams harder and assigned a sales quota of $60,000 to each team. In a perfect scenario, if all sales teams meet their sales quota, the total sales for the Sales Department would be $180,000, which is $30,000 more than the department’s sales quota. Accordingly, each team, in order to impress their Sales Manager and win a free meal and margaritas at the local Mexican joint, decided to exceed the team’s sales quota by assigning higher personal sale quotas.

In our BI solution, we are required to create an Analysis Services cube in which the Sales Manager can track these sales quotas at each level of the Sales Department organizational chart as seen in Figure1. But, we cannot simply sum up the sales quotas, because the totals would not match up. We could approach these several ways, but our requirement is to replicate Figure 1 with a single “SalesQuota” measure with no MDX or additional measures involved.

The Solution

In order to achieve the requirement imposed above we can make use of a special attribute property in Analysis Services called UnaryOperatorColumn to control how level members of our hierarchy contribute to the aggregated value of the level parent.

In a regular scenario, the parent’s sales quota at each level would be equal to the sum of its children’s sales quota. That would have been the case if the Sales Manager in our example had decided to distribute the overall Sales Department quota equally among each sales team and the individual sales team members would have done the same for their personal sales quota. In other words, each sales team’s quota would have been $50,000 each ($150,000 / 3). For Sales Team A, for example, each sales person’s quota would have been $16,66.67 each ($50,000 / 3).

But in our scenario, the parent’s sales quota at each level should not be equal to the sum of its children’s sales quota. The only solution then, is to create an “artificial” child at each level that holds the parent’s sales quota and specify this “artificial” child to be the only child contributing to the parent’s total. This is possible through the UnaryOperatorColumn attribute property in the Dimension designer in BIDS. In Figure 2 you can see the available Unary Operators that can be used in this property along with the resulting behavior as described in Books On Line (http://msdn.microsoft.com/en-us/library/ms175417.aspx).

Figure 2. Unary Operators.

The UnaryOperatorColumn attribute property value, as the name implies, is a pointer to a table column that holds one of the Unary Operators listed in Figure 2. For our Sales Department quotas example, we would need a column that holds the unary operator for each level member in our Sales Department dimension. The Sales Department dimension source table would look as shown in Figure 3.

Figure 3. Sales Department dimension source table

Notice that this table is a naturalized Parent Child table. Also, notice that an “artificial” child was created with the same name as its parent. This “artificial” child is the child that will hold the parent’s sales quota value and is the only value that will be used in the parent aggregation. The way we control this aggregation, is by assigning to this artificial child the ‘”+” Unary Operator and the rest of the children the “~” Unary Operator.

At the lower level in our hierarchy all the way to the right in Figure 3, each sales person is assigned a “~” as its Unary Operator  in the SalesPersonUnaryOperatorColumn and only the “artificial” child receives a “+” as its Unary Operator. The same applies for the Sales Team level in our hierarchy, only the “artificial” child is assigned the “+” Unary Operator.

The FactSalesQuota table that holds the sales quotas would look as shown in Figure 4:

Figure 4 FactSalesQuota fact table

The Analysis Services project

Once we have defined our underlying table structure, we can take a look at the Analysis Services project. Figure 5 shows the basic project definition with the Sales Department Dimension and Sales Quota measure.

Figure 5. Analysis Services project definition

The Sales Quota Measure

The sales quota measure is a straightforward column based measure. It is based on the Fact Sales Quota table and is a simple SUM aggregation. Figure 6 shows the sales quota measure definition.

Figure 6. Sales Quota measure

The Sales Department Dimension

The Sales Department dimension is a simple dimension with the necessary attributes needed to design a drilldown hierarchy. Figure 7 and 8 show the Sales Department dimension definition, user hierarchy and attribute relationships.

Figure 7 Sales Department definition

Figure 8 Sales Department attribute relationships

A very important step in any user hierarchy definition is to specify the key columns at each level. In this case, the only attribute that needs a composite key column definition is the Sales Team attribute. The key column definition is shown in Figure 9.

Figure 9 Sales Team attribute key columns

The deployed cube with the Sales Department definition provided so far would look as shown in Figure 10.

Figure 10. Deployed cube

Notice that the “artificial” children show up and that the children’s sales quotas are being added to the parent’s aggregated amount. To fix the aggregation issue we need to define the UnaryOperatorColumn attribute properties for the children level members. We can hide the “artificial” children by changing the HideMemberIf user hierarchy level property to Parent.

Figure 11 and 12 show the values for the UnaryOperatorColumn property for the Sales Person and Sales Team attributes.

Figure 11. Sales Person UnaryOperatorColumn value

Figure 12. Sales Team UnaryOperatorColumn value

Figure 13 and 14 show the user hierarchy level property HideMemberIf

Figure 13 Sales Person user hierarchy level HideMemberIf property

Figure 14 Sales Team user hierarchy level HideMemberIf property

The Results

Once these properties have been changed the resulting deployed cube should look exactly as required. Figure 15 shows the exact aggregation behavior as in Figure 1.

Figure 15 Final results

Conclusion and Considerations

The solution presented above provides the exact results as dictated by our requirements and is meant as a means to exemplify and understand how unary operator can be used to control aggregation behaviors in user hierarchies.

There are other options to provide the same results, but this solution requires no calculated members or MDX at all. While this solution works, you need to keep in mind that there are some performance consideration regarding unary operators and parent child hierarchies. The key is always to TEST! TEST! TEST!

Sample Files

You can download the project files used in this post here.

%d bloggers like this: