Category Archives: SQL Server BI

Discovering SSRS Report Parameters using PowerShell

We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.

Here is an example:

SSRS parameters in SSDT Preview

SSRS parameters in SSDT Preview

This particular report utilizes different variations of parameters. To review, here are the available data types for SSRS report parameters:

SSRS Parameter Data Types

SSRS Parameter Data Types

In the example above, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.
Read more

Upcoming SQL Server Events – SQL Saturday #407 Vancouver and 24HOP

A couple of upcoming SQL Server events:

24HOP

24HOP GOC 2015 (or 24 hours of PASS Growing Our Community 2015) is happening June 24-25, 2015. This event is a 24 hour event, with a session happening every hour delivered by SQL Server and Business Intelligence (BI) experts from around the world. The list of sessions are posted 24HOP 2015.

Application & Database Development

  • Common Table Expressions – Advanced
  • Database Design: Solving Problems Before they Start!
  • Every Byte Counts: Why Your Data Type Choices Matter
  • Introduction to Microsoft Azure DocumentDB
  • Offline Database Development and Unit Testing with SSDT
  • Protecting Your Data with Encryption

BI Information Delivery

  • Introduction to the Power BI Designer
  • Understanding and Visualizing Data Using R in SQL Server

BI Platform Architecture, Development & Administration

  • Don’t Repeat Yourself – An Introduction to Agile SSIS Development
  • Improving Data Analytics with Azure Machine Learning
  • Optimizing your data warehouse for OLAP Processing
  • Rethink your ETL with BIML
  • SSAS Design and Performance Best Practices
  • Cloud Application Development & Deployment
  • Azure IOT: A Practical Introduction to Stream Analytics

Enterprise Database Administration & Deployment

  • Common SQL Server Mistakes and How to Avoid Them
  • Curious Cases of Availability Groups
  • Data Pages, Allocation Units, IAM chains… Oh My!
  • Overlord – tracking changes, real-time, in your environment
  • SQL Server Agent: The life preserver for the drowning DBA
  • The Art and the Science of Designing a Mission Critical SQL Server Solution
  • The Data Loading Performance Presentation
  • Trend Analysis of SQL Error Logs: Seeing Beyond the Error
  • Turbo-Charged Transaction Logs

Professional Development

  • Making the Leap from Developer to DBA

SQLSaturday #407 in Vancouver, BC

SQLSaturday #407 in Vancouver, BC is happening on June 27, 2015 at UBC Robson Square, 800 Robson Street, Vancouver. SQLSaturdays are one day free training events for SQL Server professionals. SQL Server professionals have developed a pretty tight knit community over the years, and this is one way of sharing information, learnings, experiences and best practices on the product. This event is also volunteer-driven – the organizers and presenters volunteer their time and effort to help foster the #SQLCommunity.

Sessions delivered range from SQL Server Administration, Development, Business Intelligence Application, Business Intelligence Delivery, and Professional Development.

Register now!

SQLSaturday #407

SQLSaturday #407

Check out the schedules and sessions here: http://www.sqlsaturday.com/407/Sessions/Schedule.aspx

SQL Server 2012 – BI Semantic Model, Multidimensional vs Tabular

The landscape of Business Intelligence changes with SQL Server 2012. Microsoft has introduced a new “model” – the BI Semantic Model.
Don’t get too hung up on this word though, the BI Semantic Model is really just an umbrella terminology that says as long as a source follows the BI Semantic Model, it will be supported in the tools. This is the eventual goal anyway; for now there is still a discrepancy which tools support what.

The multidimensional model is the traditional OLAP model. Data source is a cube, that is processed from a data warehouse that usually follows a star schema. Complex calculations and queries using MDX can be done against it. It also allows write-backs.

The new tabular model is not really a “new model” because it is a relational model. Yes the same model we’ve grown accustomed to using when we query our transactional OLTP databases. The difference is, SSAS Tabular is an in-memory database. This is fueled by the xVelocity engine (previously known as Vertipaq), and it also leverages columnstore indexes. All calculations are in memory, and this makes tabular models really fast.

Read more

SQL Server 2012 x-Terms

Just finishing up my presentation for this Saturday’s Vancouver Techfest, and was putting a slide for some of the x-terms in SQL Server 2012. Just thought I’d share it:

xVelocity In Memory Analytics Engine

In SQL Server 2008 R2 – this was introduced as Vertipaq for PowerPivot for Excel (see James Beresford blog about Vertipaq)
In SQL Server 2012 this technology was integrated into Analysis Services.
In SQL Server 2012, it was also rebranded into xVelocity. The reasoning is that this is going to become part of a bigger xVelocity family for next generation performance improvements. Simran Jindal has a great diagram that illustrates this family. Thanks Simran, very helpful!

A one liner summary of xVelocity?
This is SQL Server going fast – sometimes really really fast (think 100X faster)! Read more

Creating a SQL Server 2012 BI development environment (VHD)

KIWI

Microsoft and Richard Davis have put together a great how-to document for creating a SQL Server 2012 VHD for development and experimental purposes.
How to Build a SQL Server 2012 Hyper-V Virtual Machine (KIWI build)

So if you are thinking of exploring the new SQL Server 2012 capabilities, including the new BI capabilities (PowerView! xVelocity!), head on to his site. It’s going to be a long process, but at least Richard has given us a comprehensive list of to-do’s.

Read more