Create BI Reports from Tabular Project in SQL Server 2012

Hello!

We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.

In this post, let’s create a simple no frills, no fancy excel report.

Down the series, I shall introduce you to Perspectives, Roles and many associated features for an elegant Self Service BI experience.

If you wish to see any feature like how to create a tabular structure and additional details, I suggest you follow this link here and also here.

This should help you kick start on Tabular Models.

Again, I am using the AdventureWorksDWDenali as my database to create tabular models.

Let’s create few measures in our existing tabular model, which was created for our earlier posts.

So, let’s create measures on our FactInternetSales, FactResellerSales

For FactResellerSales: Reseller_Sales, Reseller_Tax, Reseller_Freight.

For FactInternetSales:  Internet_Sales, Internet _Tax, Internet _Freight

Reseller_Tax:=SUM(FactResellerSales[TaxAmt])

Reseller_Amount:=SUM(FactResellerSales[SalesAmount])

Reseller_Freight:=SUM(FactResellerSales[Freight])

Internet_Sales:=SUM(FactInternetSales[SalesAmount])

Internet_Tax:=SUM(FactInternetSales[TaxAmt])

Internet_Freight:=SUM(FactInternetSales[Freight])

Also, please note to change the data type of the calculated measures to decimal and change the value of Show Thousand Separator to TRUE. These properties can be set in the properties window.

   

Now, if you are wondering that while developing measures in SSAS the developer had the luxury to group measures in folders. Well, in Tabular Project, this is no longer required. You can see the new groupings created either in SSMS or in Excel.

Now the usual chore, Build the project and Deploy the changes.

Once the task is complete, click on Model and now click on Analyze in Excel

(I am using the credentials of the current windows user. No Roles. No Perspective.)

Let us now create a pivot report analysis and a pivot chart for data visualization

1_SQL_Server2012_Create_BI_Reports_from_Tabular_Project

In the next post we shall see how to create such charts and tables directly from MS-Excel interface.

That’s all. Happy Learning!

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.