Category Archives: Free Training

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!

Free Training, MDX, Pragmatic Works, SSAS

Navigating Hierarchies With MDX Webinar 9/23/14 11 am EST

Heads up, everyone! I’ve got a free training event coming up on Tuesday, September 23 at 11 a.m. EST! Next Tuesday I’ll be discussing Navigating Hierarchies With MDX.

One of the great strengths of SQL Server Analysis Services is the ability to create hierarchies by defining relationships between attribute fields. In this webinar, we’ll take a look at how we can fully leverage our SSAS hierarchies in our MDX queries and calculated measures using navigational functions such as PARENT, DESCENDANTS, PARALLELPERIOD, and many more! So if you’ve ever wanted to learn more about some of the cool, navigational functions built into MDX or have had questions about creating really powerful MDX calculations, this webinar is for you!

But the best part is it’s all free training! Just get signed up and register for the event, completely free of charge. I look forward to seeing you there!

Free Training, SQL Saturday, SSAS, Tabular

SQL Saturday #298 Session Material Now Available (SSAS Tabular Models)

Another fantastic SQL Saturday event is in the books and it did not disappoint! There was a great crowd at University of North Florida in Jacksonville, FL and plenty of amazing sessions and speakers. Thank you to everyone that attended my session! I hope you enjoyed the session as much as I enjoyed presenting. It was a blast!

If you’re interested, the slide deck I used during the presentation is now available for your viewing pleasure, which you can see here.

If you have any questions or would like to contact me, you can always send me a message on Twitter or email me here. Again, thank you to all of my attendees!

Community, Free Training, SQL Saturday, SQL Server 2012, SSAS, Tabular

Learn to Design Tabular Models at SQL Saturday #298 Jacksonville, FL on 5/10/2014

I’m excited to announce that on Saturday May 10th in a couple Saturdays I’ll be speaking at SQL Saturday 298 in Jacksonville, FL! SQL Saturday in Jacksonville always has an awesome turnout and I love speaking at this event! There will be tons of great free training sessions from the likes of Devin Knight, Robert Cain, Andy Warren, Adam Jorgensen, Max Trinidad, and tons of other great experts! If you’re in the North Florida area on May 10, you need to get registered for this event!

The session I’ll be delivering is called What the Tabular??? and will start at 1:15 PM EST on Saturday, May 10th! If you’re interested in learning how to build a Tabular Model, how to follow Tabular Model design best practices, and how to decide if a Tabular Model is the right solution to your business problem, then my session What the Tabular??? is for you! It’s a great beginner sessions and will feature tons of demos and walkthroughs of the technology!

So if you’re nearby, definitely get signed-up for this awesome event! You won’t regret it!

Free Training, MDX, PASS, SSAS, Tabular

Recording Now Available For The Webinar, Choosing The Right Analysis Services: MOLAP Vs. Tabular

image

Thanks to everyone that attended Devin’s and my webinar called Choosing The Right Analysis Services: MOLAP vs. Tabular. I’m pleased to announce that the recording is now available to watch for free over at PragmaticWorks.com, so please go check it out. It’s a little less than an hour so you can watch it during your lunch break.

Also, the PowerPoint slide deck Devin and I used during the webinar is also available for viewing now! Please visit this link to download the slide deck.

Now for the questions! Many of you asked some great questions but unfortunately we ran out of time to answer all of the questions during the webinar. So here are a few of the questions we didn’t get to.

Q: How do I link if column have more than one column is key column in tabular?
A: If you need to create a composite key in a Tabular model table, you will need to create a calculated column that concatenate the columns that make up your composite key. You’ll need to do this in both tables you wish to relate. Once you’ve done that, then you can create the relationship between the two tables using your new columns.

Q: Can DAX be used to access cubes?
A: In the SQL 2012 SP1 CU4 release, DAX support for multidimensional cubes was added, so as long as you are running on SQL 2012 SP1 CU4 or later, you should be able to query cubes with DAX expressions. On a side note, MDX can also be used to query a Tabular model.

Q: Since tablular solution is many ways better than Muti Dimensional..then my question is when to go for Multi dimensional solution
A: This is one we covered extensively during the webinar. Here are some of the things to consider:

  1. How much data are you dealing with? If its too much to fit into memory for your Tabular model, then MOLAP is the way to go.
  2. Do you have a need for complex relationships? If so, MOLAP may be the answer. Role playing dimensions and many-to-many relationships are possible to create in a Tabular model, but they’re easier to create and manage in a MOLAP cube.
  3. Do you need to perform many complex calculations involving complex Scope assignments? If so, MOLAP is the answer here.

Q: Can you use a Multidimensional database as the source for a Tabular model and improve performance when creating low level granular reports?? This goes back to the performance differences between Multidimensional vs Tabular when creating granular reports.
A: You can use a Multidimensional database as a data source for a Tabular model, but I would suggest getting the data from the original source for the tabular model. If granular type queries are slow against your cube, those same queries are still going to be slow when you execute them to process your Tabular model.

Thanks to everyone that attending Devin’s and my webinar! If you have any other questions, please feel free to leave a comment or send me a message on Twitter!

Free Training, SSAS, Tabular

Building Your First Tabular Model Webinar Recording and Questionnaire Now Available

Thanks to everyone who attended my webinar on the ins and outs and the basics of building your first Tabular Model. I hope everyone learned a little something and maybe picked up a couple tips or tricks along the way.

 Click Here To View The Recording

And thanks to everyone who had questions! I apologize for not being able to get to the questions during the webinar, so here are answers to a few of the questions posted in the chat window.

Question: “Don’t use joins” – that includes views that have joins under the covers, right?
Answer: That’s correct. That’s still going to create additional stress on the relational engine at query time and the last thing you want is to run into a locking/blocking issue during processing because you have complex queries behind the tables in your Tabular Model. If you need to join to other tables to pick up other columns, I would suggest getting with your ETL people and materializing the desired columns in the table so you can eliminate the joins.

Question: Could you please explain what difference of perspective and role?
Answer: A Role is used to define member permissions for your model. The Role defines what actions a user can perform on the model, such as read or process. A Perspective is a viewable subset of the model. A Perspective is similar to a SQL Server View on a Table. It can be used to display a subset of the model to a business unit in order to make it a little more simple to navigate through the model.

Question: What is the difference between Active & Inactive relationships?
Answer: In a Tabular Model, multiple relationships can exist between two table, but to create accurate calculations, we need a single path from one table to another. This means that even though multiple relationships exist between the two tables, only one will be actively used. The inactive relationships between the tables will not be used.

Question: Can a tabular “object” once built be put in source control and deployed by IT like SSIS packages and SSAS cubes?
Answer: Yes, Tabular Models can be deployed using the SSAS Deployment Wizard. The Create Script can also be generated from a deployed copy of the database and used to deploy to another server. For more information on SSAS Tabular deployment methods, see here.

Question: is there a way to change the database name and remove the ugly guid?
Answer: The guid is only appended to the database on the workspace server. The database can be deployed with whatever name you like. You can set the database name by going to the project properties and setting the Database property. Whatever you enter in as the Database name will be the name of the database once you deploy the database and will not include the yucky guid, unless you company standards are to use the guid :).
image

Questions: Does Tabular model have superior performance over OLAP based cubes? Also can Tabular model be used for time series data?
Answers: I would say generally speaking that a Tabular Model will have much faster query response on average, which is definitely one of the strengths of an in-memory database. OLAP cubes have the ability to contain much more data and can scale out better but in my experience queries against Tabular Models are usually much faster than queries against Multidimensional databases. Tabular Models also allow for time calculations. For more information on the kinds of time intelligence functions and calculations you can create with DAX, check this out.

Once again, thank you so much to everyone who attended my webinar. If I didn’t get to answer your question, I apologize. Feel free to post your question in the comments or on the forums of BIDN.com.

And don’t forget to follow me on Twitter!

Free Training, MDX

Thanks For Attending My SQLSat192 Session!

SQLSat192Thanks to all who attended my session MDXplosion! Intro to MDX at SQL Saturday 192 in Tampa over the past weekend! It was a great event and I had a blast presenting, networking, and hanging out with some great friends. And a special thanks to all the sponsors, volunteers, and Pam Shaw for making this great event possible. 

If you’d like to download my slide deck and code examples from the presentation, you can get that here. If you have more questions about MDX and want to learn more, you can find my content on MDX here. And, of course, you can email me at dryan (at) pragmaticworks.com or find me on Twitter here.

Free Training, MDX, SQL Saturday

I’m Speaking At SQL Saturday 192 In Tampa

sqlsat192_speaking

Coming this 2nd day of March, I will be speaking at SQL Saturday #192 in Tampa, Florida! It’s going to be off the chain no doubt, as the kids say.

I’ll be presenting my session, MDXplosion! Intro To MDX. The MDX query language is a powerful tool for extracting data from your cube but can often seem confusing and overwhelming to the untrained eye. But have no fear! It’s easier than you might think. In this session, we’ll go over the basic MDX query syntax, how to navigate a hierarchy, how to create dynamic time calculations, and how to add calculations to your cube. It’s going to be a blast and I’m really looking forward to it!

If you’re in the area or can make it down, get registered for this event! It’s going to be a great time and with the opportunity for free training at this amazing quality from industry pro’s, it’d be crazy to pass on this event!

Community, Free Training, SQL Server 2012, SSIS

Notes From SQL Saturday 168: Introduction To SSIS 2012

Thank you to everyone who attended my session, “SSIS 2012 In Yo’ Face: Introduction to SSIS”! I had a great time presenting and it was a whole lot of fun. Also, a big thank you to my co-presenter, Mitchell Pearson (blog | twitter)!

Also, thanks to everyone who attended my second bonus session, “What’s New In SSIS 2012”.

For those of you interested, here is the package we created in “SSIS 2012 In Yo’ Face”. Simple place the content of the zip file in the C:\Projects\SQLSaturday168 folder and unzip the SSIS project to the directory of your choice. If you have any questions, please feel free to post them here or send me a message on Twitter (@SQLDusty).

Again, thanks to everyone who came to my sessions! I hope you enjoyed the sessions and maybe even learned a little something.

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!