Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Posts Tagged ‘crescent’

SQL Server ‘Denali’ is 2012, ‘Crescent’ is Power View

Posted by denglishbi on October 12, 2011

The name is officially set and the next version of SQL Server (SQL11) will be 2012 and the release date will be in the first half of next year.  There will be a Release Candidate (RC0) should be provided by the end of the year that will be feature complete that everyone will be able to download and test out to provide feedback.  This is very important to do so that you can report any bugs or items to the product team to review prior to the Release to Manufacture (RTM).sql server logo

Along with this the new self-service reporting tool (code named ‘Crescent’) has also received an official name of Power View.  PowerPivot provides the self-service data modeling capabilities to integrate your data to create a BI Semantic Model (BISM) and Power View provides the highly interactive data exploration tool.  One of the new features that has been added since CTP3 is the ability to have multiple views of the data within an existing rdlx file.  This reminds me of the briefing book concept that ProClarity has where you would create and save multiple analytical views.

Stay tuned for more information and postings from the community as the PASS Summit event continues through the week.  You can stay up-to-date by checking out the #sqlpass hash tag on Twitter.  If you are not at the conference you can also checkout the keynote events that are being streamed each day and recorded on the event site – PASS 2011 Summit Live Streaming.

Posted in SQL Server | Tagged: , , | Leave a Comment »

Visualizing Data with Microsoft Crescent

Posted by denglishbi on October 7, 2011

Yes, it is official, I am now an author.  Yesterday I noticed that the Visualizing Data with Microsoft Crescent book is now listed on Amazon.  I had been asked by Brian Larson if I wanted to collaborate on writing a book for the new reportCrescentBookCover authoring tool and I of course immediately said ‘YES’. I am also one of the technical editors of his other two books for Reporting Services and Delivering Business Intelligence for the new SQL11 release.  There are four of us from Superior Consulting Services that are working on the new Crescent book and so far it has been a very enjoyable experience.  I have been doing the blogging, tweeting, and presenting, so now it is on to something new and actually working towards a published book.

So some insight into the book, this book is going to cover the new reporting authoring tool ‘Crescent’ that is coming out in the next release of SQL11.  This is a new Silverlight browser based reporting experience that will be part of the Reporting Services Integrated Mode configuration with SharePoint 2010.  Not only will the book cover the reporting experience, but we will also go over the Business Intelligence Semantic Models (BISM) which is the new terminology being actively talked about now with SQL11.  In addition to this there will be samples and videos that will provide a very in-depth learning experience so that you will be able to get up-to-speed and familiar with both Crescent and BI Semantic Models.

Now you might know that ‘Crescent’ is the code name provided to this new feature in SQL11 and next week during the PASS 2011 Summit we should be able to have an official name to associate with this along with a few other SQL11 items (maybe even the product itself – 2011 or 2012?).  For the time being though this is the cover, but in the next week or so it should be updated to say ‘Visualizing Data with Microsoft …’.

So for the time being all I can say is thanks to Brian for the opportunity, thanks to my family for their understanding, and I hope that everyone will enjoy the book (and videos).  You can go ahead and pre-order your copy today Visualizing Data with Microsoft Crescent.

Posted in Reporting Services | Tagged: , , , | 4 Comments »

MN Microsoft BI User Group 2011 Q3 Follow up

Posted by denglishbi on September 20, 2011

UPDATE (9/23/2011): A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview.

Last week I presented on What’s New with BI in SQL Server Denali (SQL11) at the Minnesota BI User Group Q3 meeting with Jin Cho from Microsoft.  Granted that in only an hour and a half we couldn’t do the topic complete justice because there is just so much to cover and it helps to provide some demos to showcase the new features and enhancements that have been made.  The topics that we covered where going over Analysis Services and PowerPivot, talked about the BI Semantic Model, Columnstore Indexes, Integration Services, Data Quality Services, Reporting Services SharePoint Integration, and “Crescent”.

I wish I would have had more time to be able to demo some of the new SSIS items, but I allocated my demo slot to show the new self-service reporting authoring tool Crescent.  Each of the topics that we did talk about can easily be their own topic for presentation, so stay tuned for the upcoming MN SQL Saturday event and the next MN Microsoft BI User Group Q4 meeting in December.  There will also be a lot of content in regards to this topic at the upcoming PASS 2011 Summit next month that I would highly recommend.

You can download the presentation from my SkyDrive account or you can check out the slides available via SlideShare as well:

image

I have also setup a video that goes over the “Crescent” demo that I went over during the presentation.  I believe that when I switched laptops during the presentation that I did not share the desktop with Live Meeting, so I apologize for that and since this will not be part of the recording I put together a quick video.

Project “Crescent” Demo from Dan English on Vimeo.

A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview

For more BI highlights in the upcoming SQL Server release check out this list – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11).

Posted in Business Intelligence, SQL Server | Tagged: , , , , , , , | Leave a Comment »

Using Images in Tabular BI Semantic Models with Crescent

Posted by denglishbi on September 12, 2011

One of the really neat features with the new Reporting Services authoring tool code named ‘Crescent’ is that you can reference images directly from your model.  This can look really cool when you use the images in a card visualizations, part of a table, as tiles, and as slicers to use as filters within the report.  Here is a nice report that is part of the Hello World Picnic Sample that came out with the CTP3 release of SQL Server ‘Denali’ (download from Project Crescent Samples and download images from Sample Images and you can find information on the Crescent Samples for CTP3 in the readme here).

image

So this is really cool and provides a really nice visualization perspective to the report both from what you are looking at and to add a little color and life to the report.  When you are looking at a sales report for products, wouldn’t it be nice to see the image of the product or category that you are looking at.  So what better place to store this information than in your BI Semantic Model that you can then leverage with your reporting tool like Crescent.  So how do you do this?

Well in the current CTP3 there are a couple of different ways that you can go about this.  The first option is to store your images in say a SharePoint library and the provide the Image URL references in the model.  That is how the example above is being done.  That report is actually leveraging a PowerPivot Tabular model that was built in Excel 2010 leveraging the CTP3 release of the PowerPivot add-in which you can download here.  The second option that you have right now is to store the images in a database table and reference that way use base64-encoded strings.  Getting this option to work in CTP3 is by no means straightforward either and took me quite a while to figure out.  Just using a base64-encoded string was a puzzle and luckily Robert Bruckner provided that solution as to how to format the binary data in the database to this format.  There is a third option which is simply referencing the binary data from the database, but currently the CTP3 release of Crescent will not recognize this field type in the model at all.

So lets take a look at the two working options we have that we can use with the CTP3 release of Crescent along with the Tabular BI Semantic Models using both a PowerPivot and a Tabular Analysis Services solution as examples.

Image URL

As I mentioned, the Hello World Picnic PowerPivot model uses this setup.  So I will highlight how this is done.

  • First you need to have images available for what ever you want to display.  The download link is available above for the samples and then the instructions are provided in the information readme.
  • Next you need to place the images in a location that can be referenced as a URL in the model.  In this example they are placed in a document library within a folder.

images in sharepoint

images are stored in a SharePoint document library called Shared Documents buried down a couple of levels –> /Images/HelloWorldPicnic/

  • Then within your table in the PowerPivot Tabular model you can provide your URL to the image.  In this example they store the name of the image in the table and then use a calculated column to generate the Image URL.  In order to activate the option to set this as an Image URL in the Reporting Properties (which is in the Advanced tab – might need to turn on the Advanced Mode in the file setting first) you need to define the Table Identifier column in the table.  This means that you need to specify which column makes the table unique to identify each record (in this example it is the ItemId column).

table identifier

need to specify a table identifier – a column that defines the table and makes each record unique

image url

a calculated column is used here that includes the image name column from the table along with the rest of the string to the SharePoint location and concatenates everything together

  • Then once you have deployed your file to SharePoint you can create a Crescent report from the PowerPivot file and you will now see the field in the field list to select from along you can start leveraging this in your report design.

image

the Items Name and Drawing fields are selected from the Field List which get added to a Table

So that one wasn’t too bad.  And this is a nice option because having the images in a document library location is a nice spot where end-users can maintain these as needed and swap them out.

Okay, let’s take a look at the other option that is currently available and that is storing the images in the database and using a base64-encoded string.

Base64-Encoded String

So what if the images are already stored and available in a database table? Well there is a solution that can allow you to do this, at least if you are doing the Tabular Analysis Services project type.

  • Here we already have the images stored in a database table in as a varbinary(max) data type – just a warning, I believe that the max size for the images is 25K, I don’t believe that has changed yet, so just be aware of that in case your images do not show up. In this example we will be using the AdventureWorksDWDenali CTP3 sample database leveraging the product dimension data – you can download the sample databases and projects for Denali CTP3 here.

image

table contains a varbinary(max) column called LargePhoto that contains some images of our products

  • Once the table is imported into a Tabular Analysis Services project we need to modify the Table Properties for the product dimension and change the reference from Table Preview to Query Editor to generate our base64-encoded string that we will use in our model instead of the binary data that is already available from the LargePhoto data (this is currently not supported by Crescent right now, but will be in next release).

image

now the key here is to add the logic to generate the base64-encoded string which is the following – thanks to Robert Bruckner for providing the logic:

cast(N” as xml).value(‘xs:base64Binary(xs:hexBinary(sql:column(“[dbo].[DimProduct].LargePhoto”)))’, ‘varchar(MAX)’) as LargePhoto64

  • Next we need to once again set the Table Identifier (Product Id column – in this example the ProductKey column has been renamed to Product Id) and then set the properties on the new LargePhoto64 column for Image URL to True along with Representative Image to True

image

set the Product Id as the Table Identifier (which is actually the ProductKey column)

image

set the Image URL and Representative Image properties to True for the LargePhoto64 column

  • The next step is the hack that we have to perform to the Code portion because if you try to change the Image URL property back to False it will also set the Representative Image to False and that is not what we want.  You can right-click on the bim file in the Solution Explorer and select ‘View Code’ or pick the option from the ‘View’ file menu.  Once you are in the Code behind do a search for LargePhoto64 and it will take about three times to find the right spot we are looking for which contains the Type property setting that displays ImageURL.  We need to change this to just Image.

image

find the LargePhoto64 column where the Type property is located with ImageURL

image

now change the Type so that it does not include the valuens=”ddl300” and remove URL from the value so that it is just <Type>Image</Type>

  • Now you want to Save this change and then double click on the bim file in the Solution Explorer to go back into the Design view and deploy the database to your Tabular instance of Analysis Service.
  • Setup the BISM Connection File to you model (see my previous posting on this if you are not familiar with how to setup data connections to a Tabular BI Semantic Model – Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration) and then click on the connection file in your SharePoint library and you will now be able to utilize the LargePhoto64 column as an image in your reports.

image

the Product Name and LargePhoto64 columns were selected from the Field List and added to a Table

So there you go, not too bad, huh?  You have two different options right now for using Images in your models.  In the next release we should see direct support with Crescent in being able to utilize binary data directly instead of having to use the base64-encoded string method.  But for the time being this is what you can use.  My preference would be the Image URL.  This just seems not only more straightforward, but also easier to maintain and update, especially from an end-user standpoint.

Enjoy!

Posted in Reporting Services, SQL Server | Tagged: , , , , , | 2 Comments »

Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration

Posted by denglishbi on July 22, 2011

Update (7/22/2011): Added a note about an IE Security setting for SharePoint menus at the end of the post.  I will add a PDF of this posting either tonight or over the weekend if anyone is interested in that.

Update (7/24/2011): Added PDF download of the posting for printing, offline, and eReader availability at end of posting in the File Download section.

With the recent release of the SQL Server Denali CTP3 there have been a few changes in the installation process.  One of those is with how the SharePoint integration configuration works.  There was new post yesterday in regards to this process Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3).  In this post is goes through the steps assuming that you have installed SharePoint 2010 first.  What if you are like myself and installed all of the SQL Server components (SQL Server ‘Denali’ CTP3 Install Experience), including Reporting Services, before SharePoint 2010?  Well the process is a little different because since you didn’t have SharePoint installed first the integration process will not be completed by just installing SharePoint 2010 next.  There are a few steps you can take and I am going to go over the PowerShell option that I took through with the help of some documentation I was provided from Microsoft with previous CTP testing.

So here are the steps that I went through to get this working (this was done on a Windows 2008 R2 VM):

  1. Installed all of the SQL Server Components (installed Multidimensional SSAS and just installed the SSRS and did not configure it) – SQL Server ‘Denali’ CTP3 Install Experience
    image
  2. Next I went through the installation process again and installed an additional SSAS named instance in the Tabular mode.
  3. Then I installed SharePoint 2010:  pre-reqs and then SharePoint (but did not run the configuration wizard – unchecked the box and closed the process down after the install).  I had to actually run the pre-req install twice and had to do a reboot prior to the SharePoint install as I recall.
    image
  4. Then I installed the two SharePoint 2010 SP1 files – one for Foundation and the other for Office Server (this is going to be required to get SharePoint to work with Denali for the database engine).  I believe a reboot was required after this as well. http://support.microsoft.com/kb/2510766
  5. I then did the SQL Server install again to install PowerPivot for SharePoint creating another named instance of SSAS for PowerPivot.
  6. The next step can be done a few different ways and I picked the new PowerPivot for SharePoint Configuration Tool (need to ensure your pass phrase meets the security requirements) – this option is available in the Tools page of SQL Installation or in the Configurations folder in your SQL Server install folder in the Start Menu. This process will configure the PowerPivot integration, activate it, and create a PowerPivot SharePoint Site Collection.
  7. I then installed the ADO.NET Data Services for .NET 3.5 SP1which is required if you want to use PowerPivot against an Atom Feed in SharePoint.  A reboot is required after this install.
  8. Setup the SSRS service and application (since I installed SQL and SSRS prior to SharePoint the Integration with the add-in did not complete – because no SharePoint).
    1. Run the Microsoft SharePoint 2010 Management Shell as Administrator
    2. Type the following PowerShell command> Install-SPRSService
    3. Type the following PowerShell command> Install-SPRSServiceProxy
    4. Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      image
    5. Verify that the service is started in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Services on Server and now look for the SQL Server Reporting Services Service to see that it has now been setup and is runningimage
    6. Now we are going to create a new SQL Reporting Services Application in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Service Applications and create a new SQL Server Reporting Services Application
      image
    7. Specify a name for the Application, create a new application pool, and specify the database server name.  Down below you will also need to select the web application to associate this application with and I used the Windows Authentication for the database authentication
      image
    8. Now we should be all set and you should see the new application that you just created
      image
  9. Now you are off to the races and can create a Reports Library in SharePoint and add the content types including the new BISM Connection File for using to create Crescent Reports! You will need to install Silverlight to get the SharePoint Create window and this will be needed for Crescent as well since it is a Silverlight application.  If you do not see the Reports Library option you will want to make sure that the Enterprise Features are enabled not only at the Site Collection Level, but also at the Site level. I left out the Report Builder Model content type because from here on out it is all about the BI Semantic Model (BISM)
    image
    image
  10. Now you can create a new BISM Connection File and or Report Data Source to get started with Crescent (shown below in the respective order). After you create these you will not see them in the library.  In order to see them you can go to the Library tab in the Ribbon and select the ‘All reports and dashboards’ option for the view and then you will see the connection and data source files you have created (by default it simply displays just the reports so it is not cluttered)
    image
    image
    image
  11. Now you can launch Crescent from the context menu from the connection and data source files you have created.  In order to create these connection files and data sources I installed and deployed the Denali Samples from CodePlex.  There is one issue with the Tabular project that you need to be aware of when you want to deploy this that has been documented here – Problems with the SSAS Tabular Model Projects Denali CTP3 samples (according to the site the samples have been fixed, so if you haven’t downloaded them you should be okay).
    image
    image

So there you go, you are off to the races.  You know have SSRS in the new SharePoint Integrated Mode setup running as a SharePoint Application (no longer running as its own Windows Service, completely setup and managed by SharePoint now) and you are creating awesome reports with Crescent.

Enjoy!

NOTE: If you are having an issue in say Step 9 and you are not getting menu options in SharePoint you need to go into your IE Security Settings and enable the Active Scripting.  Once you do that you should be able to use the menu options in SharePoint.

image

File Download:

image

Posted in Reporting Services, SQL Server | Tagged: , , , , , | 12 Comments »

Project ‘Crescent’ first impression – simply amazing!

Posted by denglishbi on November 10, 2010

All I can say is ‘wow’.  During yesterday’s keynote that was streamed live from the SQL Pass Summit Amir Netz did a demonstration of the new reporting tool codenamed ‘Crescent’ that will be available in the next release of SQL11, codenamed ‘Denali’.  I was completely amazed at the visualizations and the functionality that was provided.  Being able to visualize the report instantly as you are creating it and the interactivity that is provided out-of-the-box was incredible.  The storyboard feature with PowerPoint was very nice as well.  Can’t wait until it is available to test drive which hopefully will be in the CTP2 release.

If you haven’t heard about this product yet then take a look at Robert Bruckner’s posting here Glimpse at Project Crescent.  He has FAQ information along with links and additional information that you will find very helpful.  There is a brief video you can check out on BizIntelligence.tv – Data Visualization Done Right: Project Crescent and if you didn’t get to see the keynote yesterday you can head over to the SQL PASS site and watch the on-demand video recording (fast forward about 3/4 of the way through to get to Amir Netz’s presentation) – PASS Summit 2010 Day One Live Streaming Keynote.

Kudos to the product team and can’t wait to see how this evolves over the next year.  Very exciting news as always.

Posted in SQL Server | Tagged: , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 1,758 other followers