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

Database administration

SQL Server Profiler vs Extended Events for Analysis Services (SSAS) 2016 Database Administration.

3/6/2017

1 Comment

 
I recently taught a live webinar for Pragmatic Works on SSAS Monitoring and Performance Tuning with Extended Events.  It is worth noting that Profiler is still available to us in SQL Server 2016, and continues to provide benefits that Extended Events do not.  This is how I see it.

SQL Server Profiler 2016
1.  This very fine SQL Server tool is being deprecated by Microsoft in a later version as stated on MDSN here.  The good news is that Profiler is not deprecated quite yet.  Hooray!

2.  This is still the fastest way to log SSAS query activity for immediate analysis.  You do not need to write out to a file, view said file, and import.  SQL Profiler provides a very handy viewing tool that is real time.  When needing to complete a one-off monitoring of a problematic query, SSIS package or SSRS report, this is the low hanging fruit.

3.  Granted, Profiler has a heavier "footprint" than Extended Events (i.e. it takes more system resources). Coupled with the fact that it is going away ... eventually ... it should not be considered as a long-term monitoring tool of choice.  When I say "long term monitoring" I am referring to scheduling monitoring through a SSIS package.

4.  When working with Profiler and SSAS, it is important to Show All Properties on the Events Selection tab of the Trace Properties window and mark relevant SSAS events.  Not all SSAS events are marked by default when connecting to a SSAS instance and taking the default SSAS trace.  The list of tabular and multidimensional events I use most often is given below.

Extended Events (EE) in SQL Server 2016
​1.  It is sometimes misunderstood, but EE is not a new SQL 2016 SSAS feature.  You could use XMLA in earlier SQL Server versions.  Chris Webb has a very good BI Blog post here that talks about doing this very thing in SQL 2012.

2.  What is new in SQL 2016 is the user interface for creating SSAS EE sessions.  Unfortunately, I feel that Microsoft did a half-hearted effort in this, and that is the politically correct way of saying it.  In a SSAS instance, you can create EE sessions, but you cannot edit them.  For real?  The solution is to use the GUI to setup the session, then be sure to script it out before you save it.  

3.  When working with EE for SSAS, I recommend you use a XMLA query in SQL Server Management Studio (SSMS).  Attached to this post is the XMLA for creatings, stopping, deleting and querying current EE sessions for both tabular models and multidimensional cubes.

4.  If using EE to warm your SSAS multidimensional cube cache, or to monitor user query durations and execution counts, EE and SSIS is definitely the best solution.  Use a SSIS package on a scheduled job to automatically stop, import and restart EE sessions.  Use t-sql against the resulting SQL Server database table, and SSRS to automatically report long running queries.


Profile Trace / Extended Events SSAS Events:
This is not a complete listing, but these are the events that I find most helpful when monitoring SSAS.
Multidimensional
    ExecuteMDXScriptEnd
    GetDataFromAggregations
    GetDataFromCache
    ProgressReportEnd
    QueryCubeEnd
    QueryEnd
    QuerySubcube
    QuerySubcubeVerbose

Tabular
    DAXQueryPlan
    QueryCubeEnd
    QueryEnd
    QueryExceedsMemoryLimit
    VertiPaqSEQueryEnd (using the VertiPaq Scan subclass event)

XMLA File Downloads
dba_create_stop_delete_xevents_for_multid_cubes.xmla
File Size: 4 kb
File Type: xmla
Download File

dba_create_stop_delete_xevents_for_tabular_models.xmla
File Size: 2 kb
File Type: xmla
Download File

1 Comment

Working with Oracle in SQL Server

5/24/2015

3 Comments

 
This is most assuredly a SQL Server blog site, but how often have we had to connect to an Oracle data source?  (For me, I count my time with Oracle data sources in years.)  If you are in SSMS, SSIS or SSAS and trying to get to Oracle, let me offer up some tips and tricks for success.  This blog post will cover four areas last tested with Oracle 11g Release 2 Client.
  1. Downloading
  2. Installing
  3. Connecting from SQL to Oracle
  4. Uninstalling the Oracle Client



First, Downloading the Oracle Client.
 
You will probably need both the 32-bit and 64-bit client from Oracle.   You will use the 32-bit client when developing in SSIS.  You will use the 64-bit client when running those packages as scheduled jobs.  Please do not download the Oracle client from any other source except directly from Oracle, and be sure to download the client, not the database.   The last time I needed the Oracle client, the following links worked for me:
     32-bit --> http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
    64-bit --> http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Picture
Picture
You may be prompted to create a free Oracle web account.  If you download using an Internet download accelerator, it should take no more than 15 minutes.  The result should be a “win32_11gR2_client.zip” and “win64_11gR2_client.zip” file.  If you have TFS (team foundation server), be nice and check it in for your fellow BI team members.
Picture


Second, Installing the Oracle Client. 
Start with either the 32 or 64-bit client.  
1.       To understand exactly what features you are installing, use a “Custom” installation.
2.       Keep the default language of “English" providing you are wanting an English Oracle Client

Use a “Custom” installation
Keep the default language of “English"
3.       Install both Clients to “C:\Oracle” specifying the “client32” or “client64” subfolder as shown below.
Picture
Picture
4.       Install all Oracle client components EXCEPT for Oracle Net Listener and Oracle Scheduler Agent.  .  (If you are an experienced oracle user and know how to configure these components, feel free to install; however, they are not necessary for DW development.)
Picture
Picture
5.       Accept the default port number for MTS Recovery Services
6.     If your installer is unable to check memory or disc space, you will need to “Ignore All” and perform this verification manually.



Note: If you are unsure of the Oracle prerequisite conditions, click “more details” under each check.

Picture
7.       Click <Finish>.
8.       Perform a typical configuration as you will overwrite the tnsnames.ora file.  This is discussed in the "Connecting" section following.
9.       Repeat Steps #1 to #8 for your second Client installation.
Click .
Perform a typical installation.
Picture
At this point I hear you asking, "Are we there yet?"  Hang in there with me.  We are on the home stretch!, but the next few steps are key critical to your Oracle Client success.


10. Verify that your HKEY_LOCAL_MACHINE\SOFTWARE\Oracle and HKEY_CURRENT_USER\SOFTWARE\Oracle system registries now have these entries:



11.   Verify your “Path” environment variable has your 32 bit Oracle path BEFORE your 64 bit path.
   a.   In System Properties, click the <Environment Variables> button
   b.   Find ‘Path’ in System Variables and click <Edit>.
   c.   Copy the Variable Value out to your clipboard, paste it into a Text file, and save it in case you want to rollback.


Open your
Find ‘Path’ in System Variables and click .
Your Variable Values now look something like this.  Identify your 32-bit paths, highlighted here in green.
  • c:\oracle\product\11.2.0\client64\bin;c:\oracle\product\11.2.0\client32\bin;C:\Program Files\RA2HP\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\ActivIdentity\ActivClient\;C:\Program Files…

   d.      Copy and paste your 32 bit path to sit BEFORE your 64 bit path.  Your Variable Value should now look something like this.  Make sure you don’t leave empty spaces (!).
  • c:\oracle\product\11.2.0\client32\bin;c:\oracle\product\11.2.0\client64\bin;C:\Program Files\RA2HP\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\ActivIdentity\ActivClient\;C:\Program Files…

   e.       Click <OK> to close your Edit System Variable window and save your changes.

12.       Reboot your PC to have your “Path” environment variable update take effect.


Third, Connecting to Oracle
You can use several tools to connect to Oracle.  Before beginning, verify that your “tnsnames.ora” file (located in [YourOracleHomeDirectory]\product\11.2.0\client_1\network\admin) has the correct information as shown below.

#---------------------
# My Project Name
#Replace the server name below with your Oracle server name
#Replace the IP addresses with your IP address
#------------------------
FSMISt2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 130.175.189.36)(PORT = 1523))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 130.175.189.37)(PORT = 1523))
    )
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DCM2PN)
    )
  )

1.       From Visual Studio 2010 Ultimate, choose Tools --> Connect to Database from the main menu.
Picture
2.       From SQL Developer, add a new connection.  Remember that SQL Developer does not use the tnsnames.ora, but rather Java.  If you are prompted for your jave.exe location, you will find it in [YourOracleHomeDirectory]\product\11.2.0\client_1\jdk\bin\java.exe.  Enter your connection information as shown below.
SQL Developer connection GUI
The resulting object list in SQL Developer
3.       From an Analysis Services Multidimensional project, you can add a new data source, then create a Data Source View. 
  • When create a new Data Source, make sure you select the correct Oracle driver as provided by Oracle, not Microsoft.
  • When creating a Data Source View, simply select the Oracle tables from the Data Source View Wizard.  (This may take a while depending upon your Internet connection speed and amount of available memory on your PC.)
Make sure you select the Oracle driver as provided by Oracle or Attunity, not Microsoft
Make sure you select the Oracle driver as supplied by Oracle or Attunity, not Microsoft.
4.      To connect to Oracle from SSMS (SQL Server Management Studio), you can create a linked server.  This is not shown here.




Last, Uninstalling the Oracle Client.

Sometimes your install doesn't go as planned -- like misnaming your Oracle installation folders.  Read Oracle’s instructions for uninstalling their software here --> (http://docs.oracle.com/html/B13804_02/deinstall.htm

However, here are my tips and tricks when uninstalling Oracle:  
  1. You will need to use an Oracle utility to uninstall.  In 10g and earlier you could use the Oracle Universal Installer.  If you are trying to connect an 11g install, you will need to use the uninstaller located at C:\{OracleHome}\product\11.2.0\client_1\deinstall\deinstall.bat.  (Be sure to run the bat file ‘As Administrator’ even if you are an Admin on the server.)
  2. After uninstalling, reboot your PC
  3. Upon start up, rename the Oracle directory where the Oracle software has been uninstalled but the directory and certain files remain.
  4. Delete the Oracle folder from your system registry (HKEY_LOCAL_MACHINE\SOFTWARE\Oracle and HKEY_CURRENT_USER\SOFTWARE\Oracle).  You will have quite a few Oracle entries remaining in HKEY_CLASSES_ROOT.
  5. Reinstall Oracle.
  6. Test your Oracle connection
  7. Delete the old Oracle installation folder that you renamed in Step #3 above.
3 Comments

    Categories

    All
    Columnstore
    Connecting
    Date & Time
    DBA
    Extended Events
    Indexing
    Installation
    Oracle
    Performance
    SQL 2016
    SQL Developer
    SSAS
    SSIS
    SSRS
    T-SQL

    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