• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Analysis Services

SSAS 2016 Monitoring and Performance Tuning with Extended Events webinar

3/7/2017

1 Comment

 
At the bottom of this blog post you will find the ZIP file referenced in today's webinar from Pragmatic Works.

Webinar Talking Points:
     1.  SSAS Performance Optimization 10K Foot View
     2.  Profiler vs Extended Events
     3.  SQL Server Instance QueryLog Properties
     4.  SQL Server 2016 Profiler & Extended Events Demos
     5.  Interpreting Extended Events Data
     6.  Extended Events Integration with SSIS Demo

ZIP File Contents:
     1.  Slide deck in PDF format with and without notes
     2.  XMLA scripts for creating, stopping, deleting extended events.  (Also find these files here.)
     3.  T-SQL scripts for interpreting SSAS event data.  (Also find these files here.)
     4.  Visual Studio Community Edition SSIS solution for warming multidimensional cache

Thanks for coming!!

2017-03-07_monitoring_ssas_with_extended_events_webinar.zip
File Size: 6779 kb
File Type: zip
Download File

1 Comment

Monitoring SQL Server Analysis Services (SSAS) 2016 Activity with Extended Events; Finding Formula and Storage Engine Durations with Extended Events

3/6/2017

0 Comments

 
Extended Events (EE) is a great SQL Server tool for monitoring Analysis Services, both multidimensional and tabular models.  I use EE primarily to answer five questions about SSAS.
1.  Who is actually using our tabular models and multidimensional cubes?
2.  How often is [someone] running queries?
3.  What are my longest running queries?
4.  For multidimensional cubes, and I missing aggregations?
5.  For long-running queries, is the problem my storage or formula engine?

I also like to use EE to warm the multidimensional cache after cube processing, but that is a subject for another blog post.  The subject of this blog post is really about interpreting data captured by EE.

Prerequisite: Knowledge of how to start, stop and import data from EE sessions.  If EEs are new to you, I have a blog post on EE for Analysis Services Database Administration that might be helpful.  This blog post assumes that you have already imported EE data into a SQL Server database table and are at the point of wanting to analyze that data.

Summary: If you want to jump to the finish line, download the two files I've attached to this blog post.  They are t-sql statements that read from EE SQL Server tables for both multidimensional and tabular models returning login, query durations, execution counts, storage and formula engine durations.  Enjoy!

Detail:
The most important thing to understand when reading EE data is how EE reports storage and formula engine durations.  Very simplistically, the storage engine is exactly what you think it is -- it stores the data.  The formula engine does the number crunching.  The first step in SSAS query optimization is to understand where you are paying the price of the query duration.
Multidimensional Cubes
Picture
Tabular Models
Picture
The attached t-sql documents have this all figured out for you, but I fully expect you to take these code samples and make them your own.  For example, group the resulting row set ordering by QueryDuration DESC, or select for one particular user Id.  Compare the storage and formula engine durations and email a SSRS report for percentages exceeding a predetermined amount.  Basically, use EE to be proactive in finding long-running queries and don't wait for the help desk phone to ring.

If working with multidimensional cubes, pay attention to the QuerySubcubeVerbose event.  The query text will help you identify missing aggregations.  Expert Cube Development with Multidimensional Models will give you more information on this.  My experience has been that once I make sure all necessary hierarchies are in place, all fact tables are partitioned properly, aggregations have updated with usage-based aggregations, and poorly written MDX has been revised, I rarely have to spend time in the QuerySubcubeVerbose event.  However, as a matter of last resort, this event has much complicated but informative query information.

Files for Download
dba_read_extended_events_for_multid_cubes.sql
File Size: 2 kb
File Type: sql
Download File

dba_read_extended_events_for_tabular_models.sql
File Size: 1 kb
File Type: sql
Download File

0 Comments

Excel Error: The item could not be found in the OLAP cube

11/1/2015

2 Comments

 
Issue: When working in Excel 2013 Prof Ed and a SQL Server 2012 multidimensional cube, you receive the following error when adding a hierarchy or attribute to the FILTERS section of the PivotTable Fields GUI.
Picture
The above error is usually caused by an object that was removed or renamed in the cube.  However, this was not the case for a recent client.  I Googled the error (like you probably did to even arrive at my blog site), but found nothing except that an object was deleted or removed from the cube.  As this was not our scenario, I'm doing this little write-up hoping that is will be helpful to someone in the same predicament.

Summary Solution: 
  1. Remove the offending dimension from the multidimensional cube
  2. Save your Visual Studio solution
  3. Rebuilt your Visual Studio solution.
  4. Add the dimension back into your multidimensional cube.
  5. Deploy and reprocess the cube.
  6. Test both the hierarchy and a couple of independent attributes in Excel's PivotTable Fields <FILTERS> section.

Cause of the Error: Unknown.  I have found on more than on one occasion that the underlying XML of multidimensional cubes becomes "out of sync".  I realize this isn't a very technical response, but if you have spent time comparing "working" XML code to "unworking" code, you know that sometimes it is easier to just remove and readd the object.

Resolution Logic:  In the latest  occurrence of this error, I worked through the following steps trying to find the cause before throwing in the towel and removing the dimension from the cube.  It was determined that ..
  • This error only occurred with one of two role playing dimensions.  15+ other dimensions in the cube did not produce the error.
  • This behavior happened with both hierarchies and independent attributes.
  • This error only occurred in Excel's FILTERS section of PivotTable Fields.  There was no problem filtering through COLUMNS or ROWS.
  • This behavior did not happen in a SSMS (SQL Server Management Studio) cube browser.   Both hierarchies and attributes filtered just fine.
  • This error was not measure group specific – it occurred with no measure group selected (i.e. no measures dropped into VALUES).
(And now I began looking for property differences that might cause this error...)
  • The role playing DIM that did work properly, had even a longer name than the non-functioning role playing DIM.
  • The role playing DIM that did work used an alias name.  The non-functioning role playing DIM did not.
  • The role playing DIM that did work had HierarchyUniqueNameStyle set to the default value of IncludeDimensionName.  The non-functioning role playing DIM had the default value of ExcludeDimensionName.  I found a 2nd DIM that also was set to ExcludeDimensionName, and it worked just fine in Excel's FILTERS section.
Conclusion:
It was a bit of work to remove a shared dimension from a large cube and re-add it, checking to make sure all cube relationships and properties remained the same, but in the end, this is what fixed the error.  if you have come across this same problem and have found a better solution, please post it in blog comments.  I'd like to hear your creative solution!
2 Comments

Multidimensional Cube Storage Options

5/24/2015

0 Comments

 
I initially compiled this table in a Power Point slide deck when I was studying for my MCSE BI, so most likely the original source is from a Microsoft textbook, but you can also easily find this same information on TechNet. 
(https://technet.microsoft.com/en-us/library/ms174532(v=sql.110).aspx).

I am posting this table for you because I often just want the abbreviated version and I appreciate the table view.
Picture
A few more notes on "cached" vs "DirectQuery", and DirectQuery Properties.
Picture
Picture
0 Comments

Finding Your SSAS Installed Server Mode

5/24/2015

0 Comments

 
Picture
You can read up on how to do this on MSDN here (https://msdn.microsoft.com/en-us/library/gg471594.aspx), but below is a quick summary.

The easiest option is to connect to SQL Server Management Studio and note the icon next to the server name in Object Explorer.










An alternative is the read the XML in the MSMDSRV.INI file searching for “DeploymentMode”.
                0 = multidimensional
                1 = PowerPivot for SharePoint
                2 = Tabular

Picture
0 Comments

    Categories

    All
    Cache
    DBA
    Excel
    Extended Events
    HOLAP
    MOLAP
    Multidimensional
    ROLAP
    Server Mode
    SSAS
    Tabular

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About