-
Presenting at SQL Saturday in Atlanta
May 5, 2013 / No Comments »
I'll present at SQL Saturday in Atlanta on May 18th. Initially, I was planning to talk about dashboard options with the Microsoft BI stack but the organizers had a call for more advanced content. To accommodate this request, I'll present Best Practices for Implementing an Enterprise BI Solution where I'll share proven practices harvested from real-life projects. "Learn best practices to make your organization a center of BI excellence! I'll walk you through lessons learned during our implementation of an enterprise end-to-end BI solution, which is discussed in the Records Management Firm Saves $1 Million, Gains Faster Data Access with Microsoft BI case study published by Microsoft. Working experience with the dimensional modeling and the Microsoft BI stack is assumed." I'm looking forward to seeing you on May 18th.
-
Default Parameters in Power View
May 4, 2013 / No Comments »
Scenario: You need to configure a Power View report to show data for a dynamic date, such as the current date or the last date with data. However, as it stands Power View doesn't support expressions. Workaround: Add a Boolean calculated column to the Date table that returns TRUE for the date of interest. For example, if you want the report to show data for today's date, the expression might be: =if([Date]=TODAY(), True, False) where [Date] is the column with date data type. Then, use this column as a filter in Power View. Unfortunately, this workaround has a significant limitation. If the report needs another filter on the Date table, such as to allow the user to overwrite the default filter on the current date, this approach won't work because filters on multiple attributes are interpreted as an AND condition (Date is 1/1/2013 AND TodayDate=True). Inability to specify OR filter...
-
Best Practice and Creative Data Visualization by Jen Underwood for Atlanta BI
April 29, 2013 / No Comments »
It looks like Atlanta BI Group will have a record attendance tonight with 74 people registered! Jen Underwood will present Best Practice and Creative Data Visualization. This fun, informative, and inspirational session covers both best practices and creative options for data visualization. We will showcase data visualization techniques in Excel, Power View, Reporting Services, Visio, and a variety of other Open Source projects and Third-Party data visualization offerings. The possibilities are endless with the right mix of tools, tips, and tricks.
-
Windows Azure Infrastructure Services
April 16, 2013 / No Comments »
Microsoft announced today the availability of Windows Azure Infrastructure Services which is a collective name for running Virtual Machines and Virtual Networks in the cloud. Scott Guthrie's blog on this subject is very informative. Pricing get slashed too to be competitive with Amazon. What's interesting is that these cloud VMs can be configured as an extension to your existing network. In the BI world, this would allow us to source data from existing on-premises data sources albeit probably over a much slower connection, such as to host your ETL, data mart and SSAS in the cloud or just the analytical layer. Speaking of connection speeds, the Azure bandwidth is actually good (5-15 GB/s) although it's likely that is likely you'll hit a bottleneck with your ISP on your way to and back from the cloud. And, speaking of BI, check the SQL Server Business Intelligence in Windows Azure Virtual Machines...
-
About Tableau 8
April 5, 2013 / 5 Comments »
Jen Underwood highly recommended I attend the Tableau 8 tour on Tuesday to witness firsthand its new features. Naturally, I couldn't resist of comparing everything I saw with Microsoft BI. I took some time after the pretention to take 8.0 for a spin and reconfirm my understanding. Here are the top five things I liked about Tableau. Simplicity – A few months ago, I blogged about my top 5 Microsoft BI wish list. My number 1 wish was a continued focus on integration and simplification. Tableau 8.0 nailed it down as far as simplicity, at least on the visualization side of things. One desktop tool and its server-based counterpart. A tool whose sole purpose is BI. Not something that was bolted on another tool as a BI add-on. No layers to integrate with and configure on the server side, and error logs to sieve through. Visualization - Visualizations are yet...
-
Data Warehouse Physical Design Best Practices Session by Carlos Rodrigues at Atlanta BI Tonight
March 25, 2013 / No Comments »
It looks like Carlos will deliver another slam tonight with a record number of some 63 people registered and counting… Join our Atlanta BI meeting at 6:30 to see his Data Warehouse Physical Design Best Practices presentation.
-
Query Options for Analytical Models and Transactional Reporting
March 24, 2013 / No Comments »
Requesting both historical and transactional reports is a very common requirement. As they stand, neither Multidimensional nor Tabular are designed to support well transactional (detail-level) reporting with large datasets, as I discussed in my Transactional Reporting with Tabular blog post. There are two query options that might provide some relief with transactional reporting and they both have limitations. DRILLTHROUGH QUERIES You can use a DRILLTHROUGH query to request data at the lowest level. The syntax differs somewhat between Multidimensional and Tabular. Here is a sample drillthrough query to Adventure Works cube: DRILLTHROUGH MAXROWS 1000 SELECT FROM [Adventure Works] WHERE ([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories],[Date].[Calendar Year].&[2006]) RETURN [$Product].[Product], [$Date].[Date], [Reseller Sales].[Reseller Sales Amount], [Reseller Sales].[Reseller Tax Amount] And a similar query to Adventure Works Tabular: DRILLTHROUGH MAXROWS 1000 SELECT FROM [Model] WHERE ([Measures].[Reseller Total Sales],[Product].[Category].[Category].&[Accessories],[Date].[Calendar].[Year].&[2005]) RETURN [$Product].[Product Name], [$Date].[Date], [$Reseller Sales].[Sales Amount], [$Reseller Sales].[Tax Amount] Notice that the WHERE clause specifies the coordinate...
-
Transactional Reporting with Tabular
March 18, 2013 / No Comments »
Scenario: We had a requirement to replace the existing implementation of transactional reporting over large data volumes. By "transactional reporting" I mean allowing the user to query individual transactions as they're stored in the fact table. In some cases, this style of reporting requires simply reading data without aggregations. This, of course is what RDBMS are designed for but in our case, requirements call for extending the reporting model with metadata about the item, such as the item barcode, alias, etc., as well as supporting fast aggregation analysis (for trend reports) and data security. In general, when implementing BI solutions, you should always have an analytical layer between the database and presentation layer for the reasons I discussed in the "Why an Analytical Layer?" blog. Challenges: Because of the in-memory nature of Tabular and more relaxed schema requirements, such as no separation of dimension and fact tables and ability to...
-
Improving Tabular Design Experience
March 14, 2013 / No Comments »
When you develop an organizational Tabular model in SSDT, there is always an implicit processing phase for each action you perform, such as renaming columns, creating hierarchies, changing formatting, and so on. This "data-driven" paradigm could be both a blessing and a curse. A blessing, because you always work with data and you don't have to explicitly process the model to see the effect of the changes. And a curse, because each time you make a change the UI blocks until the change is committed and this can get old pretty soon. Note: As long as you don't use the Table Properties dialog or explicitly refresh the data, all changes are done directly in the Tabular database and Tabular doesn't re-query the data source to refresh the data. While waiting for Microsoft to make the necessary changes, here are a few tips to improve your Tabular design experience: In my...
-
When Developers and BI Collide
March 9, 2013 / No Comments »
I've been running in this situation quite often so I thought this will make a good topic for a blog. Scenario: Management has asked for some sort of a BI solution, such as a dashboard. BI hasn't happened to the organization in question yet. But they have smart developers and there is no project that they can't do. As the story goes, developers go to work and whip out some code… lots of it. Code for doing ETL, code for the database layer, and code for implementing the presentation layer, such as as a custom web application with cool third-party widgets. Everyone is happy... at least for a while. I don't have an issue with developers. In fact, I spent most of my career writing code. However, there are several pitfalls with this approach so let's mention some of them: Custom code is expensive to write and maintain – Developers...
Training
We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.For more information or to register click here!
Books
Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.
