Just another Blog

My Technical, Professional and at times Personal Blog

Showing posts with label Denali. Show all posts

Project Juneau: SQL Server Database Development and ADO.NET entity framework

 

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.

image

choose database project:

image

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.

image

Create association between entities:

image

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.

image

Create Stored Procedures directly in Visual Studio and get rich intellisense support:

image

Create Triggers:

image

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.

image

Once you create your snapshot, you can then publish your changes to your database:

image

image

image

Output:

image

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.

image

Note the Code diff below:

image

Debugging your application lets you step through your Database Stored Procedures/Triggers within Visual Studio.

image

Start Debugging:

image

image

Database Projects can be easily deployed to SQL Azure.

image

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.

image

Publish Database to SQL Azure:

image

References:

http://blogs.msdn.com/b/ssdt/

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV314

Next Generation Tools for Microsoft SQL Server Analysis Services in Denali

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.

image

Document KPIs: these will appear as annotations in the PowerPivot field list.

image

View KPIs in Excel:

image

You can restore this PowerPivot Excel workbook directly in Analysis Services. This will import everything we built within PowerPivot: model, tables, data, KPIs etc

image

image

PowerPivot Model is imported in Analysis Services as a Database.

image

Business Intelligence Development Studio (BIDS) now available Visual Studio 2010:

image

When you create a new Semantic Model in Visual Studio, the system creates a new workspace database in Analysis Services:

image

You have the same table import wizard in BIDS that you have in PowerPivot:

image

pick the table to import:

image

After the table is imported in BIDS:

image

Create multiple measure by one click:

image

Create Models in BIDS:

image

Easily Create Hierarchies:

image

Product Hierarchy in PowerPivot:

image

Create perspectives by one click:

image

Create copies of perspectives easily and check the measures and dimensions you don’t need:

image

Work with Perspectives in BIDS:

image

View the Model in PowerPivot:

image

Security:

Apply row level security using the Role Manager. Use DAX filters to return true or false.

image

image

Deploy Models: Models can be deployed to Analysis Services.

image

New Analysis Services database is created:

image

Microsoft BI: Project Crescent in Denali

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:

image

Launch from SharePoint Shared Documents if the PowerPivot workbook is uploaded to the SharePoint:

image

List of Data Sources available to SharePoint:

image

Create connections to BI Semantic Model:

image

Crescent Interface:

image

image

Add filters to the report:

image

Easily Add Interactive Charts:

image

different types of charts:

image

Advanced Visualization for maximum use of screen real-estate: I like the concept of card view Smile. Wonder where we could use it in a business application – perhaps create a report with Order: Order Detail on it.

image

Card View:

image

Crescent Architecture:

image

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.

Scatter Graph:

image

Line Graphs:

image

Multiple Graphs on a page are automatically connected! No property pages to connect all of them together:

image

Security needs to be defined at PowerPivot/Analysis Services level and not in crescent.

Google+ Followers