by Mark Kromer Late last year, Microsoft announced the availability of an updated beta or community technology preview (CTP) of SQL Server Reporting Services in the Cloud based on the Microsoft Azure platform: http://blogs.msdn.com/b/windowsazure/archive/2011/10/13/announcing-sql-azure-reporting-preview-release.aspx. The product is formally known as SQL Azure Reporting Services and if you’d like information about the preview, visit the link above for all the details. I just wanted to spend a few minutes today introducing you to the new CTP and what I’ve found there so far. I’ve built a Cloud BI Web-based sample app and a Windows Phone 7 sample app using SQL Azure Reporting Services and I promise that I will blog about how you can build one, too, in the next coming months. This should be a natural progression of my Microsoft Cloud BI: All the Pieces series. One thing that I really liked in this CTP is that I am able to manage users so that I was able to make demo users who can access my reports from my Web-based Cloud BI solutions with a read-only report user style account. You can also upload and download RDL files to and from the cloud and your laptop from the Azure management screen: I was not able to access the normal SSRS styled Report Manager link from SQL Azure Reporting Services. But the Azure Management Screen also contains an area within the Silverlight console that looks like the Report Manager console and has similar functions like when you select a report and use the drop-down menu in the Details view of Report Manager: The Report Server Web Services URL is available in this CTP, just as it was in the previous CTP. Of course, a key tenet and advantage of any area of the Azure platform from Microsoft is the fact that all of my existing developer and administrator tools will essentially still work the same in Azure as they do today in the traditional on-premises world meaning that I won’t need to learn new tools or new design paradigm...
Read the rest of entry >>
By Mark Kromer We’re coming up to the end of 2011 and it’s been a while since I’ve posted here on the SQL Mag BI Blog. In fact, in the 1.5 months sine I’ve posted here, the site’s name has changed to SQL Server Pro and SQL Server 2012 Release Candidate (RC0) was released! Well, since this is a BI blog for SQL Server, it only makes to end the year in style. Let me introduce you briefly to perhaps the most highly-anticipated new BI feature and new tool in SQL Server 2012, Power View: http://technet.microsoft.com/en-us/library/hh213579(SQL.110).aspx. What is Power View? Power View is a brand new Silverlight browser-based reporting tool that is currently scheduled to ship with SQL Server 2012 Enterprise Edition and the new Business Intelligence editions, once general availability hits in 2012. This is the current plan as of RC0: http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx. You access the reporting tool from a SharePoint 2010 library or BI site and it is geared toward business users. You get one surface to design on and to report from. There is no click-once client or separate tool to download for designing and viewing. That is still the general model for the existing Report Builder and SSRS reporting tools in SQL Server and those still exist with their own enhancements in SQL Server 2012. But the biggest and most exciting advances in SSRS 2012 are in the Power View tool. I’ve used Silverlight components in the past and Derek and I have references different ways to utilize Silverlight in your BI applications here on this blog. Power View gives you that power and interactive data exploration capability out of the box, meaning that your business users can be much more engaged with data assets in the data warehouse or anywhere in your organization, which encourages better decision making. If you have used the SSRS tools in the past like BIDS with Visual Studio or Report Builder, then you will notice a bit of a paradigm change with Power Vie...
Read the rest of entry >>
by Mark Kromer For those SQL Server and development shops that have built a lot of SQL Server Integration Services (SSIS) packages out there and have utilized Visual Studio Team Foundation Server or Visual SourceSafe to provide version control over those SSIS packages, you will be glad to learn about the new versioning capabilities in SQL Server v-next, code named “Denali”. In fact, when I’ve been trying out the new version of SSIS, I have found the overall deployment experience to be a huge step forward over the 2005/2008 deployment model with many advances in terms of configuration management and deployment to a server. For today, I’m just going to focus on version control, because it’s been difficult in the past to get something working that is easy to configure and get working. And it is very nice to see this now natively in the product. In the picture below, you will see that a new node is available in the Object Explorer tree in SQL Server Management Studio (SSMS) that is set aside just for Integration Services. You can still log in directly to the SSIS service from SSMS, but that is only going to show SSIS packages that were deployed in the old, more arduous model, which you will see referred to as “Legacy Model” in BIDS. The new model is simplified and wizard-driven. The first thing you have to do is to set-up an SSIS “Catalog” which stores SSIS “Projects” that then store SSIS “Packages”. Now when you right-click on the Projects node in the tree, you will see a “Versions” menu item which launches the screen below. Note that on this screen you will see the project versions, which one is active, descriptions and the option to rollback to an earlier version with “Restore to Selected Version”. You will see 2 packages in my project on the screenshot to the left. Notice that “Project” is essentially project-level from BIDS, which is now Visual Studio 2010 in Denali. When I rolled back to my initial version of the project, I lose the 2nd pack...
Read the rest of entry >>
by Mark Kromer I’ve been a long-time advocate and big proponent of having a mechanism in my end-to-end BI solution that can show me what has changed and what will changed BEFORE something breaks. By end-to-end BI solution I mean from the data source to the ETL to the data mart to semantic layer to the scorecards and dashboards. If something changes anywhere in my complex system of data integration and data analysis, I need the system to either self-heal (ultimate vision!) or at least flag this and let me know the impact. In fact, back when I was a BI consultant and PM for BI solutions, we used to include a custom-built Microsoft Console Snap-In that would collect your solution’s metadata from database to scorecard, store that data about your data and allow you to analyze changes flagged as red for breaking change, yellow for caution, etc. SQL Server Codename Denali (SQL Server v-next) had the original public beta as CTP1 last year that included a “Dependency Service” which is meant to be a SQL Server based lineage and impact-analysis tool. You will notice that in CTP3, that service is gone from Object Explorer. But have no fear! For those Microsoft BI solution architects out there, if you have not seen it yet, Microsoft is still working on this capability under the project name “Project Barcelona”. This is their blog here and I recommend you visit it if you would like to have a sneak peak at the tool and find out the latest from the project team’s efforts. One thing that is really cool on their blog is that they have a link to where you can try out and provide feedback on the UI. below is a screen capture sample of the latest UI that they have put up publicly for comment: You should check it out and submit your feedback. Even if you don’t car...
Read the rest of entry >>
by Mark Kromer In the world of data warehouse specific databases, column-oriented databases (as opposed to the classic row-based databases) are becoming very popular. One of the most popular pure-play column-oriented databases is from Vertica and Oracle databases with Exadata storage includes hybrid columnar compression, which maintains the row structure, but compresses the data very effectively with common values in columns. The next release of SQL Server (11.0 or Denali, if you like) will include Microsoft’s first release of their database engine to include columnar compression, which is being surfaced to database developers and DBAs as columnar indexes. The technology that Microsoft is using inside SQL Server for the column-oriented compression and indexing is based on the same Vertipaq technology that is currently in use in PowerPivot and being expanded upon in Denali as a secondary Analysis Services engine that will provide an alternative to the MDX/UDM engine. Much of what I am describing here is fully expanded upon in this whitepaper by Microsoft’s Eric Hanson which I highly recommend that you download and read. It is brief and very effective. What I want to also highlight in terms of the Microsoft implementation of columnstore indexes is that the data on the data pages in a columnstore index are stored as pure column storage, not as a hybrid row and column model. The example columnstore index from that whitepaper is reproduced for you here: CREATE COLUMNSTORE INDEX cstore on [dbo].[catalog_sales] ([cs_sold_date_sk] ,[cs_sold_time_sk] ,[cs_ship_date_sk] ,[cs_bill_customer_sk] ,[cs_bill_cdemo_sk] . . . Basically, what they are doing is adding ALL rows from the fact table of their star schema data warehouse data model in this index. The query optimizer will make the determination of using the columnstore index or another index or heap for you. I’ve also copied a screenshot of how you can acc...
Read the rest of entry >>
by Mark Kromer Here is a quick peek at some of the enhancements to Master Data Services (MDS) in SQL Server Denali as of CTP3. Before I do that,though, let’s take a look at what MDS is from a SQL Server product perspective … MDS is Microsoft’s MDM tool that is included in SQL Server 2008 R2 Enterprise Edition and above. The lineage of the product is from the Microsoft acquisition of Stratature and the MDS release in SQL Server 2008 R2 was the first release of the Statature IP with Microsoft. MDS is meant to be a tool that you can use to build data domains around your critical business entities like products, customers and employees and to use MDS as the tool in your master data management (MDM) projects to provide centralized data governance that creates single version of the truth repositories. The final step is to integrate MDS with your operational and analytical systems with common Microsoft data integration tools like SSIS and BizTalk. What MDS in Denali CTP3 provides is a big step forward in terms of the usability, applicability and analytical MDM by integrating with a brand new capability in the SQL Server data management suite called Data Quality Services (DQS). Ok, so without further ado, I will get you started with 3 new features in MDS in SQL Server Denali CTP3 (download it here) that I’ll highlight and then you can go and explore the rest of the suite in CTP3. Improved Web UI I’ll be honest, no matter what product I’m talking about, saying that a new version has an “improved Web UI” is just simply not a major feature of the product. An example is the way that Oracle has been touting the UI improvements in Fusion Applications over the Web-based navigation UI on some of their ERP apps. The MDS Web UI is meant as a tool for data stewards and so, like ERP systems, they are not going to be glamorous user interfaces. I’ve pasted a screen capture below which doesn’t really do it justice. But what is important to note is that if yo...
Read the rest of entry >>
by Mark Kromer We all know that data warehouses aren’t “real time”, right? I mean, a warehouse is there to store data for historical analysis, after all. But the struggle to separate operational reporting against an ODS or a reporting schema from BI reporting against a data warehouse continues to confound IT and business organizations. The “real time” data warehouse and analytics can be PowerPivot, after all, or smaller functionally-confined data marts. But if your data warehouse has a business requirement to be more “real time” than, say, the classic nightly load or even hourly, then I typically recommend using the “trickle-feed” or incremental update approach. In SQL Server 2008 R2, there are 2 mechanisms built into the product that you can use to accommodate this, provided that your data sources are SQL Server databases. So, let’s take a brief look at Change Tracking and Change Data Capture or CDC: Change Tracking This is a feature that is built into the query engine in SQL Server and is available in both the Standard Edition and Enterprise Editions of SQL Server. You do not have the kind of control over the tracking mechanisms that CDC provides and SQL Server performs some behind-the-scenes magic by adding flags to your rows once you’ve enabled tracking on. You can see the difference in the bytes per row on your tables when you enable change tracking on a table using sp_spaceused. Before you start building out your data warehouse ETL, you will enable change tracking on your database and then on each table to track with these commands: ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON ( CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON ) GO ALTER TABLE dbo.Products ENABLE CHANGE_TRACKING GO As you can see...
Read the rest of entry >>
by Mark Kromer Well, readers, all good things must come to an end. This is part 5, the final part of my series where I’ve walked you through the tools, techniques and pieces of the Microsoft Cloud BI puzzle that you’ll need to start developing your own Microsoft BI solutions that are Cloud (mostly, perhaps a bit hybrid) BI solutions. We’ve talked about created data marts using Microsoft’s cloud database, SQL Azure, based off your large on-premises SQL Server data warehouse using SSIS or da...
Read the rest of entry >>
by Mark Kromer It is now time for part 4 of my 5-part series where I am walking you through the different Microsoft product and solution pieces to build a Microsoft Cloud BI solution. There are a few parts that I’ve called out thus far that are “hybrid”, i.e. not yet fully cloud-based. But today’s installment is going to focus on the presentation layer and we’re going to deploy these dashboards solely in the cloud, via Microsoft’s Azure platform. First, we’re going to use the new CTP (beta) of Azure Reporting Services to host a simple AdventureWorks dashboard that I built using Report Builder 3.0 in part 3 of this series. And, yes, that is currently a client tool that you need to have a local copy of to make this work. So we’re still quasi-complete in Cloud BI, still somewhat hybrid. But I’ll use our local copy of Visual Studio 2010 to build a simple ASP.NET application that will include the ReportViewer control hosting that Azure-based report, deployed in Windows Azure. Then another ASP.NET app will include a very simple, rudimentary example of a Silverlight control to display data from our SQL Azure database. Both of these presentation methods of Cloud BI use the same tools that you use today to build the classic SQL Server based dashboards and reports – Report Builder, Reporting Services, SQL Server, Visual Studio, Silverlight and ASP.NET. Instead, I’m just using SQL Azure, Windows Azure and deploying into the cloud instead of on a local server. Already being familiar with these tools and languages (T-SQL, MDX, .NET and C#), make the transition into cloud with Microsoft’s Azure platform quick & quite easy. Let’s start with the ReportViewer control in Visual Studio with SQL Azure. Again, not much different than what you can do today where you host an ASP.NET application that has the ReportViewer control embedded in a form and you point that control to your report. The report is usually best to have hosted in SQL Server 2008 R2’s Reporting Services (SSRS). ...
Read the rest of entry >>
By Mark Kromer In part 3 of this 5 part series, Mark examines the new Azure Reporting Services CTP (beta) that enables SSRS-like reporting services in the cloud with Windows Azure & SQL Azure. So far, in part 1 & part 2, we’ve talked about migrating data into data marts in SQL Azure and then running analysis against that data from on-premises tools that natively connect into the SQL Azure cloud database like PowerPivot for Excel. That approach can be thought of as a “hybrid” approach because PowerPivot is still requiring on-premises local infrastructure, such as PowerPivot, for the reporting. Now we’re going to build a dashboard that will exist solely in the cloud in Microsoft’s new Azure Reporting Services. This part of the Azure platform is only in an early limited CTP (beta), so you will need to go to the Microsoft Connect site to request access to the CTP: http://connect.microsoft.com/sqlazurectps. Think of Azure Reporting Services as SSRS in the cloud. You will author reports using the normal SQL Server Reporting Services 2008 R2 tools. In this demo, I’m going to use the business user ad-hoc reporting tool called Report Builder 3.0. One of the great benefits of the overall Microsoft cloud platform called Azure, is that you can develop applications and databases and you can migrate to Azure from your on-premises solutions to Azure very easily. The tools that you are already familiar with: Visual Studio, SQL Server Management Studio, BIDS, Report Builder, SQL Server Management Studio, all work with SQL Azure and Windows Azure. For example, in Report Builder, you will design a report just as you normally would and enter in the URL for the Azure Reporting Services as the site to store or retrieve the report definition: Design the report based on a data source that is from SQL Azure, since this is a pure Cloud BI solution: Whether you are using Report Builder or BIDS in Visual Studio, you will enter the Azure s...
Read the rest of entry >>