A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Sunday, November 11, 2012

Top 10 Things I Learned While NOT at PASS 2012

Last week was the PASS Summit in Seattle, Washington. I was unable to attend this year, but I watched the keynote addresses on PASS TV, a live streaming video feed from the conference. Normally I post a "Top 10 Things I Learned" from each day of the Summit. Because I didn't go this year, I didn't do this. But that is not to say I learned nothing last week. In fact, I spent last week developing my first functioning "data mart" for one key area of the Shelby v.5 application: GlobaFILE. GlobaFILE is the core repository of names and name-related data. It does not have any true data warehouse style "facts" in it, such as financial transactions, but it does serve as the basis of core name content for all other modules apart from the General Ledger. The biggest bonus to creating this data mart is the ability to capture changes over time, which is by itself a pretty big win for historical analysis.

While I learned last week is a combination of aspects of SSIS, T-SQL, and news from PASS during the keynote addresses. Without further ado, here are my top 10, in no particular order:

1. SSIS: I learned that most of the expressions used in SSIS are based on C# syntax rather than on SQL or VB.NET. This was a frustration because it is yet another language syntax I will need to learn in order to work effectively in SSIS.

2. SSIS: Most of the data transformation I need to do from my OLTP database to the as-yet-in-design OLAP database cannot be done with the basic SSIS data tasks. I would need to use C# conditional statements and VB.NET scripts together to achieve the result. But by the same token, I can do most of the data transformation with T-SQL in queries. So right now I'm not certain if SSIS is really an essential part of the development of a Shelby v.5 data warehouse solution, especially in light of the next Top 10 item.

3. T-SQL: The MERGE command in T-SQL can populate slowly changing dimensions and facts in the data mart. I am glad that I already knew how to create Common Table Expressions prior to attempting to populate my data mart database using MERGE. The CTEs are where the data extraction and transformation take place, and the MERGE statement itself is where the data are loaded into the data mart tables.

4. T-SQL: Define the surrogate key column of a fact or dimension table with the "identity" property so that it will auto-increment a new value for every new row. For a dimension table, specify 0 as the first value for the "identity" assignment, thus enabling a manual INSERT query to populate the "N/A" row.

5. T-SQL: Define a "trigger" on a dimension table in order to automatically assign a "RowEnd" date to any row that is supplanted with a new row with the identical business key.

6. T-SQL: Use calculated columns to make explicit some data that are stored implicitly in other columns but which will make the end-user's experience much easier to have at hand, such as "age" and "RowCurrent."

7. SSIS: Unless I'm missing something, deploying solutions from SSIS to a production server context is much more difficult than deploying reports in SSRS to the Report Manager. This is another reason that I am currently preferring using MERGE over SSIS.

8. KEYNOTE ANNOUNCEMENT: Microsoft is in development of an enhancement to SQL Server 2012 called "Hekaton" that will optimize selected tables and procedures so that they run completely in memory, yielding improvements in efficiency of one to two orders of magnitude.

9. KEYNOTE ANNOUNCEMENT: Excel 2013 includes a completely stand-alone instance of Power View visualization reporting. This is huge. This means that some reporting options that were reserved only for owners of SQL Server Enterprise AND SharePoint Enterprise are not available to any owner of Excel 2013. I found Office 2013 available on TechNet downloads, and I can't wait to get it installed and make sure I understood the announcement correctly. If I did, this could be a game changer for reporting at all levels, including for the churches who use Shelby v.5 and Microsoft Office.

10. BONUS: It pays to tweet. As part of a Twitter contest sponsored by PASS, I tweeted what I liked best about PASS Summit 2012 (which was the PASS TV live feed of certain sessions of the Summit). And I was one of two winners of the contest! :-) I don't yet know what I won, but I'm hoping for the USB key with videos of all the sessions of the Summit on it. It's a long shot, since that's listed as a "grand prize," but I'm keeping my fingers crossed until I hear something, hopefully this week!

Next year PASS will be in Charlotte, North Carolina, and I'm definitely planning on going next year. My next PASS event is coming up much sooner: I'm speaking next month at SQL Saturday in Washington, D.C. on December 8th. Hope to see you there!