Tag Archives: free training

#SQLFamily, Data Warehouse Design, Free Training, PASS, Power BI, SQL Saturday

Data Warehouse Design and Power BI at SQL Saturday Tampa 2019

SQLSaturday #859 - Tampa 2019

Hello, fellow data nerds! I wanted to make you aware of a fantastic event coming up this month in Tampa, Florida. On Saturday, March 23, 2019, the annual SQL Saturday event will be hosted at the University of South Florida! This is a really tremendous opportunity to engage in some quality training at little to no cost. If you’re able to be in the Tampa area Saturday 3/23 then you need to register for this event. I’ve seen the schedule and the line up is impressive! Old dogs and young dogs alike will learn something new from a host of professionals and industry experts!

I’ll be presenting two sessions during the morning of March 23 at SQL Saturday Tampa and I’d love to see you there!

Making Sense of the Power BI Ecosystem

Power BI has taken the world by storm since it’s release in mid-2015. Since then, Microsoft’s cloud analytics service has matured and grown in scope and complexity. In this session, we’ll seek to understand the moving parts of the Power BI ecosystem including Power BI Pro, Premium, Report Server, Embedded, Data Flows, and more. By the end of this session the audience should have a firm grasp on the components ecosystem and how those components work together.

Building a Data Warehouse from the Ground Up

What if you could approach any business process in your organization and quickly design an effective and optimal dimensional model using a standardized step-by-step method? In this session we’ll discuss the steps required to design a unified dimensional model that is optimized for reporting and follows widely accepted best practices. We’ll also discuss how the design of our dimensional model affects a semantic modeling technologies like SQL Server Analysis Services or Power BI and how the choices we make during the data warehouse design phase can make or break our reports.

I hope you can make it to this really amazing event and can make it to one of my sessions! Don’t forget to register! If you do, swing by and say hi to me!

Free Training, MDX, Pragmatic Works, SSAS

Navigating Hierarchies with MDX webinar recording is now available!

image Thanks to everyone that attended my webinar on Navigating Hierarchies with MDX! We looked at a bunch of different ways we can navigate up, down, and side to side in our hierarchies in order to do some really neat things with calculations. If you would like to view the recording, you can do that here completely for free! Also, if you’d like to view my PowerPoint slide deck and scripts I used for the webinar, you can download those from here. Just download the Navigating Hierarchies with MDX .zip file. 🙂

Now on to the questions!

Q: Is there anything like storeproc / pre stroed mdx query in SSAS which can be called in .net application.
A: There is a concept of SSAS stored procedures, which you can read more about here: http://msdn.microsoft.com/en-us/library/ms176113.aspx and here: http://asstoredprocedures.codeplex.com/

Q: How would ParallelPeriod handle a leap year?
A: ParallelPeriod returns the member at the same position in the specified period. So if the 29th day of February does not exist in the previous year, then no value will be returned:
image

Q: Can you use PeriodsToDate() on a ‘custom’ period like an Academic Term?
A: PeriodsToDate can be used on any user defined hierarchy.

Q: Setting date property for MTD, QTD seems straightforward.  What about WTD (week-to-date)?  It seems it might take some careful work each year.
A: If you have a Week attribute in your Date dimension, that should be set to Week, as well. That’s all that is required. 🙂
image

Q: What is the name of the zoom tool and highlighter used, just curious.
A: Zoomit. It’s free, too! I get asked that question every time I present.

Thanks for all the great questions, everyone! If you have any further questions, please feel free to post it here or to send me a tweet!

Excel, Free Training, PowerPivot, Pragmatic Works

PowerPivot 101 Training Webinar with Q&A

2 Comments

To watch the free recording of my webinar called PowerPivot 101, just head over to PragmaticWorks.com, create a free login and you’re all set! Please let me know if you have any questions.

PowerPivot 101 free webinar training

Once again, thanks to everyone that attended my session on PowerPivot. Here are my responses to some of the questions I received during my session but did not have time to answer until now.

1. Does the current version of PP support drill through?

The current version of PowerPivot does support drill through. All you have to do is right-click a measure in your spreadsheet and select Show More Details or alternatively you can double-click the measure.

image

2. Is PowerPivot add-in required for SharePoint?

PowerPivot for SharePoint is required if you wish you share Excel workbooks that contain PowerPivot data via SharePoint. For more information on installing and setting up PowerPivot for SharePoint, check this out.

3. Where will a replay of the PowerPivot 101 webinar be available?

Head over to the Pragmatic Works Learning Center to view the recording of this webinar as well as the recordings of all the previous webinars!

4. Book/website recommendations?

Definitely check out PowerPivotPro.com run by Rob Collie. It’s a great website with tons of cool PowerPivot-y stuff, plus Rob is a cool dude.

Check out these books!

image4

PowerPivot for Excel 2010: Give Your Data Meaning
Marco Russo & Alberto Ferrari

image10

Practical PowerPivot & DAX Formulas for Excel 2010
Art Tennick

5. What is the name of the component/function in SharePoint that would make the “live” data connections possible in published PowerPivot?

I assume the question is directed at utilizing a live connection to a data source, such as SQL Server. To my knowledge, this is not natively supported with PowerPivot. The data in your PowerPivot workbook must be refreshed.

There are two ways to refresh data in a PowerPivot workbook. You can refresh it manually anytime you want or you can schedule a refresh through SharePoint like I showed during the webinar. For more information on refreshing data in a PowerPivot workbook, check out MSDN.

6. I thought DAX was only supported using the Tabular mode of SSAS. My understanding is that PowerPivot is built on the Multidimensional model. I wouldn’t think DAX would be supported. Can you comment?

DAX (Data Analysis Expression language) is the formula language for PowerPivot and Tabular modeling and is used to specify calculations and to create new columns. It’s basically an extension of the Excel formula language. For more information related to DAX in regards to PowerPivot and Tabular modeling, read this and this.

7. Can you blog about having a calculated field that I can put on a slicer to filter by number of records (i.e. products with more than 100 records).

To filter records where a measure is greater than or less than a certain amount is pretty easy. In your workbook, just click the drop down arrow above the attribute you wish to filter, go to Value Filters, and select Greater Than, Less Than, or whichever option you desire.

image

Then you can select which measure you want to use and what quantity to use.

image

Thanks for all the great questions everyone. Don’t forget to check the recording of the webinar in case you missed anything!