This blog post is compiled from a presentation on Project Juneau in Teched 2011 and it highlights the key features of the product.
SQL Server development is now deeply integrated with Visual Studio Development environment. It enables developers to write T-SQL while working on Entity-Framework projects.
We start by adding an Entity Data Model (edmx) to our project.
choose database project:
Changes to the Data Model (edmx) are instantly propagated to the database. The idea here being, you build the database as you build your application. You don’t need to spend additional time later to synch up the application with an existing data model.
Create association between entities:
No initial setup of database is required. Juneau creates a localDB to work with using SQL Server Denali’s feature called Local DB Runtime. The local DB Runtime is a on-demand, file-based local DB instance that can be used with the Visual Studio Project for debugging and testing.
Create Stored Procedures directly in Visual Studio and get rich intellisense support:
Create Database Snapshots using SQLX files and refer them in your app. Shapshot is a point-in-time reference of your database in your project. You can then refer the SQLX Snapshot than the database project – similar to referring a DLL in your project. Snapshots can also be created from a live database.
Once you create your snapshot, you can then publish your changes to your database:
After deployment, you can continue to work on your application without your database by referring to the SQLX snapshot. With Snapshots, we can also do a schema compare – this is helpful in identifying the changes between the two snapshots.
Note the Code diff below:
Debugging your application lets you step through your Database Stored Procedures/Triggers within Visual Studio.
Database Projects can be easily deployed to SQL Azure.
As soon as you set the target platform to SQL Azure, the Visual Studio validates the project to ensure we are not violating any constraint that Azure has.
Publish Database to SQL Azure:
Great presentation by Julie Strauss at Microsoft Tech-ed’11. Download slides from here. I have captured the key learning here.
KPI designer inside PowerPivot:
KPI designer lets you create KPIs in a PowerPivot Model and lets you reuse it.
Document KPIs: these will appear as annotations in the PowerPivot field list.
View KPIs in Excel:
You can restore this PowerPivot Excel workbook directly in Analysis Services. This will import everything we built within PowerPivot: model, tables, data, KPIs etc
PowerPivot Model is imported in Analysis Services as a Database.
Business Intelligence Development Studio (BIDS) now available Visual Studio 2010:
When you create a new Semantic Model in Visual Studio, the system creates a new workspace database in Analysis Services:
You have the same table import wizard in BIDS that you have in PowerPivot:
pick the table to import:
After the table is imported in BIDS:
Create multiple measure by one click:
Create Models in BIDS:
Easily Create Hierarchies:
Product Hierarchy in PowerPivot:
Create perspectives by one click:
Create copies of perspectives easily and check the measures and dimensions you don’t need:
Work with Perspectives in BIDS:
View the Model in PowerPivot:
Apply row level security using the Role Manager. Use DAX filters to return true or false.
Deploy Models: Models can be deployed to Analysis Services.
New Analysis Services database is created:
Good presentation on Project Crescent by Sean Boon, Carolyn Chau, Sabrena McBride at Microsoft Tech-ed’11. You can download the slides here. I have captured the key learning here.
Project “Crescent” is an interactive data exploration and visual presentation experience designed with end users in mind. Crescent is an easy to use, Silverlight based application delivered through SharePoint 2010 (Crescent will not work with SharePoint 2007). It is a blend of Office products like PowerPoint and Excel.Crescent is metadata driven and fully integrated with PowerPivot and SharePoint. It is NOT a replacement for any of the other Microsoft BI products such as Reporting Services, Analysis Services etc but is considered a new entrant (add-on) to the Microsoft BI stack. While Report Builder is targeted towards IT Professionals and Power Users, Crescent is targeted at end users due to its ease of use and data visualization features. Report Designer on the other hand has a much sophisticated design environment and is therefore suited for Developers.
Crescent only works in Reporting Services in SharePoint Integrated mode. There are two ways to launch Crescent: (1) PowerPivot Gallery (2)
Launch using PowerPivot Gallery:
Launch from SharePoint Shared Documents if the PowerPivot workbook is uploaded to the SharePoint:
List of Data Sources available to SharePoint:
Create connections to BI Semantic Model:
Add filters to the report:
Easily Add Interactive Charts:
different types of charts:
Advanced Visualization for maximum use of screen real-estate: I like the concept of card view . Wonder where we could use it in a business application – perhaps create a report with Order: Order Detail on it.
Crescent is integrated with SharePoint. We can connect to a PowerPivot Model in SharePoint or connect to a BI Semantic Model in Analysis Services. You can also get data directly from SQL Server using the analysis services with a feature called Direct Query. A recommendation if we are going to query directly against SQL Server is create column store indexes which is based on vertipaq to get better query performance. The column store is a new Index for SQL Server relational. Crescent is a read-only tool and there is no write-back feature. Currently Microsoft wants all its BI technologies to be used with SharePoint rather than standalone BI apps. Personally I think this is a brilliant strategy since SharePoint is pervasive within an organization, end users are already familiar with the SharePoint interface.For example, when we publish an Excel Workbook in SharePoint, we get Excel Services. When we publish a Reporting Services Reports in SharePoint we get Reporting Services in SharePoint mode.
Multiple Graphs on a page are automatically connected! No property pages to connect all of them together:
Security needs to be defined at PowerPivot/Analysis Services level and not in crescent.