Press "Enter" to skip to content

Day: February 22, 2016

Power Pivot And Power BI Data Modeling

Published 2016-02-22 by Kevin Feasel

Avi Singh has a post on data modeling in Power Pivot and Power BI:

It was Greg, who suggested that we form a book reading club. Our first book was one I had heard about, but never read – The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling By Ralph Kimball. As a business analyst, I had leaned heavily on Excel, along with a mishmash of other technologies. Data warehouse and data modeling didn’t seem like topics that would be relevant to me; more for an IT/BI team perhaps. But I figured, it couldn’t hurt to learn something new.

Our book club meetings looked more as if, class was in session. We brought in our questions, and Greg patiently answered them, helping us realize the importance of the topics, and trade-offs involved in various choices. As things go, our reading club was disbanded before we were even halfway through the book. But the knowledge that I had gained, helped me grow by leaps and bounds in my Power Pivot and Power BI journey.

Kimball-style fact-dimensional modeling remains a brilliant solution.

Comments closed

More On Widening Identity Columns

Published 2016-02-22 by Kevin Feasel

Aaron Bertrand has part 3 in his series on identity columns:

This post investigated two potential workarounds to either buy you time before changing your existing IDENTITY column, or abandoning IDENTITY altogether right now in favor of a SEQUENCE. If neither of these workarounds are acceptable to you, please watch for part 4, where we’ll tackle this problem head-on.

This is your weekly reminder to plan for appropriate data sizes.

Comments closed

Getting Into Power BI

Published 2016-02-22 by Kevin Feasel

Jorge Segarra jumps into Power BI:

For my contribution to this contest I’ve decided to share with you a work in progress. If you know me, I’m a huge lover of Policy-Based Management. In fact, I’m actually part of the Enterprise Policy Management Framework (EPMF) project on Codeplex. T-SQL Tuesday event is normally a DBA-centric event so I figured I’d help the DBA crowd wrap their heads around how a BI solution can help them in their day to day.

What I did to kick start this effort was to create this Power BI report that allows you to explore the database repository that contains the EPMF policy evaluation results. The current EPMF project uses Reporting Services to deliver its reports. This won’t change. If anything I’ll be exploring new capabilities with SQL Server 2016 and R-integration. Here’s a screenshot of what the SSRS dashboard report looks like:

I like this post because most Power BI examples tend to be personal (Fitbit stats, etc.) or business-y.  This is a good example of a use of Power BI for back-office database administrators.

Comments closed

T-SQL Medians

Published 2016-02-22 by Kevin Feasel

Daniel Hutmacher has a post showing how to calculate medians and percentiles in T-SQL:

Medians as a concept are simple enough. If you have a large number of values, like a range of statistical values, you want to pick the middle one. The median, as opposed to the average is useful for a number of reasons, one of them that you can reduce the effect of so-called outlier values.

The fact that SQL Server doesn’t have a fast, built-in median function surprises me, to be honest.  The best alternative I’ve found was a CLR function in SQL#.

Comments closed

Logical Data Models

Published 2016-02-22 by Kevin Feasel

Kevin Kline discusses logical data modeling:

In a recent blog post entitled Is Logical Data Modeling Dead?, Karen Lopez (b | t) comments on the trends in the data modeling discipline and shares her own processes and preferences for logical data modeling (LDM). Her key point is that LDMs are on the decline primarily because they (and their creators) have failed to adapt to changing development processes and trends.

I love all things data modeling. I found data models to be a soothing and reassuring roadmap that underpinned the requirements analysis and spec writing of the Dev team, as well as a supremely informative artifact of the Dev process which I would constantly refer to when writing new T-SQL code and performing maintenance. However, as time has passed, I have been surprised by how far it has fallen out of favor.

This is an interesting discussion.  I’m not sure I’ve ever created a true logical data model.  I’ve worked with systems which could potentially take advantage of them, but they never hit the top of the priority list.

Comments closed