Implementing Tabular Data Models: Microsoft Virtual Academy

 

Implementing Tabular Data Models

Date: November 11, 2014
Time: 9am‒5pm PST
Where: Live, online virtual classroom:
http://www.microsoftvirtualacademy.com/liveevents/implementing-tabular-data-models
Cost: Free!

Register today! (linkto: http://www.microsoftvirtualacademy.com/liveevents/implementing-tabular-data-models)

What will you do with all that data?

What about building corporate BI solutions, creating self-service analysis, and setting up custom reporting? In “Implementing Tabular Data Models,” on November 11, we’ll take a detailed look at in-memory databases, known as tabular data models, supported by SQL Server Analysis tools. Get practical insights from the experts, including Julie Koesmarno, fresh from the SQL PASS Summit 2014, and popular author Patrick LeBlanc.

Learn to enrich the data model to meet analytical requirements, get the details on DAX queries for custom reporting, and hear tips and techniques for near real-time solutions. Watch a step-by-step build of an actual tabular data model, and hear about common pitfalls. Let us help you make sense of all that data!

Course outline

  • Introduction to Developing Tabular Model in SQL Server Data Tools (SSDT)​
  • Designing the Data Model in Tabular
  • Enriching the Data Model with Calculated Measures and Data Analysis in DAX
  • Overview of Querying Data Model with DAX
  • Administering and Securing Tabular Model
  • Near Real-Time Strategy in Tabular Model
  • Gotchas, Tips, and Tricks in Tabular Model Development

Talk to you soon,

Patrick LeBlanc

Uncategorized

Parameterizing an Excel Spreadsheet

I’ve had a few discussions in the past week about not only including Excel spreadsheets on PerformancePoint dashboards, but specifically about how to pass values from Filters to the spreadsheet. I did a few demonstrations to show the capability to some customers. Immediately after a few of the demonstrations I received a emails asking me to explain how I accomplished passing the filters to the spreadsheet. I would explain that I added a parameter to the spreadsheet before I saved it to SharePoint. Each time the customer would say, I did not know that was possible. As a result, I have decided to document the steps in this blog.

The first step is to build the spreadsheet. I have created a very simple sheet that is connected to an SSAS Tabular model. On the sheet is a Pivot table and a single filter, which can be seen in the following image:

Now the most important part is to name the Filter, which will act as the parameter, and to name the Pivot table. Naming the Pivot table is optional. However, if you don’t want to display the filter in the dashboard it is necessary. You can also simple hide row on which the filter resides. Completely up to you.

To name either you need to click FORMULAS from the Excel ribbon. Click in the cell that contains the values of the filter and select Define Name from the Defined Names section of the ribbon and the New Name window will appear. In the textbox labeled Name enter a value. Ensure that Workbook is selected in the Scope drop down list and click OK. The window should resemble the following:

You can repeat these steps for the Pivot table if you don’t want to display the filter. Now all you need to do is save the workbook in a document library in SharePoint. Select File | Save As. From the Save As options select SharePoint and browse to the location where the file will be saved. The Save As window will open. Click the Browser View Options button located towards the bottom of the window and a Browser View Options window will open, which can be seen below:

Select Items in the Workbook from the drop down list. If you have named the Pivot Table select it from the All Named Ranges item list. Next click on the tab labeled Parameters. Click on the button labeled Add and the Add Parameters window will appear:

Click the checkbox next to the parameter and click OK twice. Then save the workbook.

Finally, you should use PerformancePoint dashboard Designer to build a dashboard that will display the Pivot Table. Prior to creating the dashboard you should create a filter that contains the same data as the parameter (the filter that you added to the Pivot table) and you must create a report that displays the excel pivot table that you deployed to SharePoint. Once that is done, create a dashboard and add both the filter and the report to the dashboard. Once they are both there you need to create a connection on the report to the filter. To do so, click the drop down arrow located to the right of the report and select Create Connection:

Select the item that contains the filter.

Next, click the tab labeled Values. Select your parameter name from the drop down list labeled Connect to. Select Member Unique Name from the drop down list labeled Source value and click OK. Finally, right-click on the dashboard in the Workspace Browser, which is located on the left of the Dashboard Designer and select Deploy to SharePoint from the context menu that appears. When the deployment completes the dashboard will open in a web browser. Select a value from the filter and the spreadsheet values will update based on the selection.

As always, if you have any questions or concerns regarding this post, please feel free to email me at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012

The Tale of One Tabular Model and Excel 2013 with Multiple Power View Reports

SQL Server 2012 Reporting Services introduced Power View, which was initially available only in SharePoint. This limited the use of Power View only to those individuals and organizations that had SharePoint deployed in some environment. To make Power View more widely available it is now an add-in for Excel 2013. Now anyone with Excel 2013 can create reports and dashboards using Power View. This leads me to the story.

Recently I was working with a customer and they were leveraging Excel to create Power View reports using an SSAS Tabular model as the source data. While this a good choice the problem was that they could not figure out how to connect multiple Power View reports in the same work book to a single tabular model. Now if you have ever worked with data in Excel you should be familiar with the Import Data window, which can be seen below.

When you initially connect to a tabular model using Excel 2013 the Import Data window gives you a few choices, one being Power View. After selecting Power View a new tab is added, but more importantly to the right of the screen you will see the Power View Field list, as seen below:

This is pretty simple and intuitive. Most Excel users can quickly figure this out. However, what if you want to add another sheet connected to the same tabular model? How is that accomplished? Well first click Insert in the ribbon, move the cursor to the right and you will see the Power View Reports button. If you click it a new Power View sheet is added, but you may notice that the Power View Field list resembles the following:

What the….. Where is the data? How do I connect to the existing tabular model connection? Well the first thing that I see most people do is click Data in the ribbon, but most of the items or greyed out. What next? The first step is to go to a regular sheet in the workbook. You know, one with rows and columns. Then click on Data in the ribbon. You should see most of the items enabled. Locate and click the Existing Connections button. The Existing Connections window will open, which can be seen below:

At the very top you will see a list of connections that are in the workbook. Select the connection of choice and click the button labeled Open. When you do the Import Data window will appear. Simply choose Power View from the list of available choices and a new Power View sheet will be added to the workbook. Most importantly, you will have a field list that is fully populated with the information from tabular model.

Now you can create several Power View reports based on a single connection. As always email me at pleblanc@sqllunch.com if you have any questions or concerns regarding this post.

Talk to you soon,

Patrick LeBlanc

 

Uncategorized

SSRS 101 – Your First Report

I know that it has been a while, and I apologize. I have been traveling around the U.S. evangelizing SQL Server to public schools, colleges and universities. Now that does not excuse my time away from the blog, but that is my excuse and I am sticking to it. Anyway, back to SSRS. If you have been following along you are ready to create your first report. That is exactly what I am going to demonstrate in this blog post.

To get started open the solution from the last post in this series, SSRS 101 – Creating a Shard Dataset. If you don’t have the project click HERE to download it. SSRS allows you create very simple reports and very complex reports. In this post I am going to create a very simple report that contains a single object and displays some data. In future posts I will expand on this report by adding additional visualizations.

Watch video if you prefer:


Creating Your First Report

  1. If you haven’t open the solution that I have been using throughout this series.
  2. Open the Solution Explorer if it is not open.
  3. Right-click the folder labeled Reports, select Add | New Item…

     


     

  4. Select Report from the Add New Item window that opens.
  5. In the textbox labeled Name enter Dashboard.rdl.
  6. Click the button labeled Add.
  7. Place your cursor on the new report that appears on the Design tab and left-click.
  8. In the menu select View | Report Data, which is the last item in the list.
  9. The Report Data window will appear. There are five folder available. I will discuss each at some point in this series. For now, right-click the folder labeled Datasets and select Add Dataset….
  10. The Dataset Properties window will open. In the textbox labeled Name enter SchoolAbsencesAndTardies.
  11. Ensure that the radio button labeled Use a shared dataset is selected.
  12. In the list of available shared datasets select Get_SchoolsAbsencesAndTardies_Aggr.
  13. Click the button labeled OK.

     


     

  14. The data set will now be displayed and all the columns that are available.
  15. In the menu bar select View | Toolbox.
  16. Select and drag a Matrix from the toolbox onto the report design surface.
  17. Open the Report Data window as explained in step 8. You could also simple click the report data tab, which should be located towards the bottom of the toolbox.

     


     

  18. Click School_Name and drag it into the column labeled Rows in the Matrix.
  19. Click School_Year and drag it into the column labeled Columns in the Matrix.
  20. Click Tardy and drag it into the column labeled Data in the Matrix.
  21. Expand the School_Name column a little so that can display the values on one row without wrapping.

     


     

  22. Click the Preview tab that is located directly above the report design surface.
  23. Since the Stored Procedure that we used in the dataset has a parameter so will the report. Before previewing the report you will be required to enter a value. Enter NW and click the button labeled Preview that is located to the right. Your report will execute.

     


  24. Save all of your work.

That’s it, your first report is created. In the next post I will explain how to customize the parameter to make it a little more user-friendly. Until then, happy report writing. As always, if you have any questions or concerns regarding this posts or any of the preview posts please feel free to email me at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc, Microsoft, Technical Solutions Professional SQL Server and Business Intelligence

 

SQL Server 2012, SQL Server Reporting Services

Configuring PerformancePoint in SharePoint 2013

Last week while assisting a customer configure SharePoint BI I ran into an interesting problem when configuring PerformancePoint. After following all the steps precisely on this page:

Configure PerformancePoint Services: http://technet.microsoft.com/en-us/library/ee748644.aspx

We opened the dashboard designer. Next we started to create a Data Source to an SSAS Tabular model, but when we expanded the database list it was empty. What? I have done this a million times over, what am I missing? I stopped and paused for a bit and somewhere in the back of my memory I remember installing ADMOMD.NET from the SQL Server SQL Server 2008 R2 Feature Pack. After the installed completed we reset IIS, restarted the dashboard designer, and the database list populated.

Hope this helps someone.

Talk to you soon,

Patrick LeBlanc

Uncategorized

Creating a Shared Dataset with Report Builder

I was recently asked by a customer if you could build a Shared Dataset using Report Builder 3.0. More specifically, can you build a Shared Dataset that combines multiple tables from the same database? To be honest, I wasn’t sure initially because I never attempted to. However, after a little testing I found out that it was absolutely possible. In this post I will demonstrate how to accomplish this. I will be using the ContosoSchoolsDW database.

NOTE:
Similar to creating a Shared Dataset using SQL Server Data Tools (SSDT), you must first create a Shared Data Source.

Watch the video if you prefer:

Step-By-Step

  1. Open Report Builder.
  2. Select New Dataset from the list of choices.
  3. Then either select a previously used data source or click the Browse other data sources… hyperlink and locate the data source of choice.
  4. After you have selected a Data Source click the button in the bottom right labeled Create.

  5. On the left of the screen you will see the Database view. You can build a dataset based on Tables, Stored Procedures or Table-values Functions. For now expand the folder labeled Tables.

    NOTE: Before I proceed understand that Foreign Keys are essential to using the Query Designer to create the query for the dataset. While you can write a query from scratch by clicking the button labeled Edit as Text, you must be proficient with corresponding data sources programming language no matter if it is TSQL, PLSQL, etc…. In addition, you can also manually add relationships by expanding the relationships section, which is located between the Selected fields and Applied filters section, but this requires an intimate understanding of the underlying Database schema. Since I know that the appropriate Foreign Keys exists in the selected data source using the Query Designer will be sufficient.

  6. Expand Dim_School and select School_Name from the list of available columns.
  7. Expand Dim_Time and select Year_Name from the list of available columns.
  8. Expand Fact_Student_Absence and select Tardy from the list of available columns.

    NOTE: If you want to see the TSQL that the designer is generate click the Edit as Text button in the ribbon. Also, if you would like to aggregate the data you can do so by clicking in the column next to the desired field and select the aggregation type from the list of available values.

  9. Click the filter icon located midway down the window on the right.
  10. Click in the Field name column and expand the Fact_Student_Absence table.
  11. Select Tardy from the list of available columns.
  12. Click in the Operate column and select is not from the list of available values.
  13. Click in the Value column and select null from the list of available values.
  14. Click the save button at the top of the window and the Save As Dataset window will open.
  15. Enter StudentAbsence.rsd in the textbox labeled Name.
  16. Browse to a location where you have been granted permissions to save datasets and click the button labeled OK.

Now if you create a new report the dataset can be used as a source for that report.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012, SQL Server Reporting Services

SQL Lunch – GeoFlow: 3D Geospatial Visualization

Join me next month to learn about a new Geospatial visualization feature of Excel 2013 on the SQL Lunch.

Lunch Details

#75-GeoFlow: 3D Geospatial Visualization

Speaker: Julie Koesmarno

Add To Outlook: Add To Calendar

Date and Time: 5/10/2013 11:30:00 AM CST

BIO: Julie Koesmarno is a consultant in SQL and BI space, with a Bachelor’s degree in Information Technology specializing in software development. She has over 8 years of commercial experience in SQL Server for large-scale and multi-million-dollar OLTP and ETL projects as a SQL Server developer and system analyst/designer. Her recent project includes implementing a pilot Data Warehouse project using SQL Server 2012, with Tabular Model. She regularly blogs (www.mssqlgirl.com) on SQL Server and BI, particularly Tabular Model.


Topic: #75-GeoFlow: 3D Geospatial Visualization
Have you seen PowerView map features? Do you think it is cool because of its self-service BI aspect and ease of use? Wait until you see this 3D Geospatial Visualization tool called GeoFlow (Preview). In this session, you will learn how to use GeoFlow. You will learn some of the limitations of the Preview version of GeoFlow. Finally, you will learn how to transform location based data into, more than just a report, but a visually engaging tour!

If you would like to present on the SQL Lunch or want to see a particular topic presented please feel free to email me at pleblanc@sqllunch.com.

Join if you have time,

Patrick LeBlanc, founder SQLLunch

SQL Lunch

Introduction to SQL Server 2012 – Webcast Resources

First, I would like to thank everyone for joining my webcasts today. Very much appreciated. Secondly, I would like to apologize for not being able to show the AlwaysOn demonstration. Shortly, after the webcast I found out that we lost internet connectivity to our lab. They are currently working on it.

Now to business, during the webcast I promised to provide you all with the scripts that was used during the presentation and some other documents and links. Here you go:

Webcast Downloads

Scripts

PowerPoint

AlwaysOn Project and Backup

Documents and Links

AlwaysOn Virtual Step-By-Step

New SQL Server 2012 XML – Scroll down about half way and you will see the documentation on XML updates.

New SQL Server 2012 Reporting Services

Extended Events

www.sqllunch.com

http://msbiacademy.com/

Again thanks for joining and I hope you all find this information helpful.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012 Step-By-Step

AlwaysOn, Speaking

SQL Lunch – Power View Against Multidimensional Models

It has been some time since I have hosted or presented on the SQL Lunch. Work and home life is a balancing act where home typically wins. In the case of the SQL Lunch home is winning more and more every day. However, I am committed to delivering or hosting a single SQL Lunch each month. This month’s topic is Power View Against Multidimensional Models.

Lunch Details

Speaker: Patrick LeBlanc

Add To Outlook: Add To Calendar

Date and Time: 3/20/2013 11:30:00 AM CST

Topic: #74-Power View reports against Multidimensional models
Learn how to build reports using Power View against your Multidimensional models. In this session, you will learn how SQL Server Analysis Services exposes the multidimensional model in a tabular view so that Power View and other clients can send DAX queries against multidimensional models. You will also learn new features in Power View that help you to gain insight into your complex multidimensional models.

 

If you would like to present on the SQL Lunch or want to see a particular topic presented please feel free to email me at pleblanc@sqllunch.com.

Join if you have time,

Patrick LeBlanc, founder SQLLunch

SQL Server 2012, SSAS

Successful BI for Educational Institutions

Do you work for US Education? Are you interested in Learning about Microsoft BI? If so, please join me as I discuss and demonstrate how the MS BI Suite can be used in your environment to discover and uncover hidden GEMS within your existing data structures.

Everyone likes a pretty picture, but the key to successful BI projects is the culture change required to adopt a data-driven process.  Culture means everyone, and getting people to “do BI” has proven less successful than putting BI into what they already do.�This session will focus on the familiar tools that can be used to deploy secure, trusted, personalized data to users across your institution, tools that most already own.  Featuring many customer examples and lessons learned.

Register today! 

For the most up to date listing of webcasts or to view others on-demand visit our site
Thank you and we look forward to having you at this upcoming webcast! 


Talk to you soon Patrick LeBlanc

US Education SQL Server and BI Technical Specialist

Learn More at
www.microsoft.com/education

SQL Server 2012