Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

Microsoft BI tools: How they use data sources

A quick list of how each of these Microsoft BI tools handles the two data sources “SQL Server” (relational-based) and “Analysis Services” (multidimensional-based):

  • Report Builder – Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (by selected “Auto Detect” relationships on the “Design a query” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, and has the benefit of not needing to create joins as a cube has them built-in
  • PowerPivot –  Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (via “Select Related Tables” button on the “Table Import Wizard” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different Table Import Wizard, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in, but the result returns just one flattened table.  I like to think of PowerPivot as essentially a way of making an analysis services cube from a relational source using Excel as the design tool
  • PerformancePoint – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  “SQL Server” can only be used to represent tables as KPIs on scorecards or have them appear as data values within filters (see http://www.jamesserra.com/archive/2012/10/using-performancepoint-against-tabular-data/)
  • Excel Pivot Tables – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  If use “SQL Server”, can only use one table
  • Power View – Can only connect to the Tabular model and the multidimensional model (which is in CTP, see Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP)
  • Visual Studio Reporting Services (SSRS) - Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (when adding tables on the “Query Designer” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...