Automate SSAS partitions in SSIS:

https://dataqueen.unlimitedviz.com/2014/05/how-to-automate-ssas-cube-partitioning-in-ssis/

Clear SSAS cache

Select new XMLA Query in management studio. Right click the cube and go to properties. Make sure you make a note of the cube database id as this can be different than what the cube is named as

Replace the CubeDATABASEID and SUBCUBENAME with the correct values and run the following:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>
<DatabaseID>CUBEDATABASEID</DatabaseID>
<CubeID>SUBCUBENAME</CubeID>
</Object>
</ClearCache>

Refresh SSRS Cache

e use several heavy reports in Microsoft Reporting Services to show data to the business users, and it really saves the day to cache them. Caching means that the server saves a temporary “snapshot” of the report in it’s cache so it will popup immediately! The performance improvement is great, but there is one problem. I don’t know exactly when the data is “ready to serve”, and I need to refresh the cached reports dynamically. With dynamically I mean, parameter based. I also need to cleanup the SSRS Cache first.

In Microsoft Reporting Services, there are two options for pre-loading Reports; caching and snapshots. A snapshot is like a piece of paper, a static instance of the report. After the snapshot is taken, nothing can be changed in the parameters. Caching is different. It can save different “instances” of the report (same report with different parameters configurations) and can save this into the TempDB till it expires. A cached instance will be cleaned from cache after it expires. There is no way you can “Re-Cache” things (only with an advanced cache refresh plan, out of scope). When you re-run caching, SSRS looks in the database for the Instance and sees that it is already cached and will leave the old results in database. It will do this with the parameters given (fingerprint). This is a very important thing to understand. A report won’t stay in the cache indefinitely. It will be removed if the report is set to expire, if the report is modified, if the parameters are modified, if the credentials of the data source change, if any other execution options change, or if you delete the report.

So one thing to keep in mind with Null Delivery is that the copy of the report in cache must be expired before you create a new copy using this method. I would suggest setting up the report cache to expire on a specific schedule, shortly before this subscription runs.

There are two methods of getting (not refreshing, this is a wrong term) the data of the cache or a snapshot; specific on a time OR with a shared schedule. As you can see, none of these options work for me, so I looked a bit in detail in what options I have to refresh the cache manually.

Concept: we use two shared schedules, these are dummy shared schedules. We will attach the reports we want to cache/expire to this shared schedules. The first one we use to trigger report cache expiration. The second one we use for caching the results. At the end of our datawarehouse load, we trigger the shared schedules at a specific moment,. All the caching of the attached reports will be deleted and will be refilled!

I will take you through the steps to get the job done!

Step 1 – Create two dummy shared schedules

You need proper permission on the SSRS manager to get the “Site Settings” button on top of report manager:

Click on the Site Settings, Schedules, New Schedule. Give it a meaningfull name, something like

“DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete” “DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete”

We use this to activate caching and cleaning cach for all reports.

You cannot set “Run Once” with a runtime in the past, otherwise it will never work. This wil cause the subscription to expire immediately after the caching has run!  I discovered this because my caching didn’t work. It wil create duplicate caching records in database with the same hash value, alle expired. So my conclusion is that SSRS will check the Shared Schedule attached to the “expire value” to verify when it needs to expire. When this shared schedule is already expired, the cache is immediately expired when it’s saved to the table, the data is 1970-01-01!

 

SELECT C.Name, EC.* FROM ReportServerTempDB.dbo.ExecutionCache AS EC INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

[message_box title=”Important” color=”red”]Please give the Shared Schedule a valid value. Expired shared schedules do NOT work!!. For example; create one with a date starting yesterdag and run it ones a year, 31th of december.[/message_box]

The Next run is in future, schedule also and expired column is empty! Double check!

SSRS will create an agent job for this schedule. You can view this job in the Agent. The scheduling of this job will be set to the time you have defined. You can also check the step it creates! For now, we delete this job. It pollute your agent job tasks and we don’t start the job via the agent. We will start the job in SSIS at the end of our ETL process.

 

 

Step 2 – Setup a NULL Delivery Subscription

Go to the SSRS report (via report manager) which is performing bad, this one is going to be setup for caching via a NULL Delivery subscription.

 

 

Click on the “Subscription” tab and create a new data driven subcription of type “Null Delivery Provider”.

You need a SQL Server Enterprise License to use this feature. When the button is not there, you probably have the Standard Edition.

 

 

Note that you have to enter the password every time you edit the subscription.

 

As you can see, I use a database view. In this view I can generate all parameter combinations for which I want to cache the report. This can be just one line of a lot of lines. Example;

 

Press Validate to check if everything is fine. Press next, next.

Now map every parameter from the view or sql command to the report parameter. Every combination will be cached. Each time you edit this subscription, you have to complete the connection password.

In the final step, we attach this subscription to the dummy shared schedule:

Step 3 – Confgure Caching

Go back to the “Manage Report” screen and choose “Processing Options” on the left. Choose:

Cache a temporary copy of the report. Expire copy of report on the following schedule:Shared schedule -> the schedule we created in the first step, the expire shared schedule.

Step 4 – Running the Shared Schedules

Now we have setup a Data Driven Subscription of Type “NULL Delivery”. Nothing will be send by mail, just generate the report for caching purposes. The next thing is to give the shared schedule a kick so it will run. This can be done using the AddEvent procedure against the Report Server database. First we retrieve the GUID of t he dummy shared schedule. Then we add the SharedSchedule event to the event table. SSRS scans this table every second and will execute it! But important, first clean the cache of all reports, attached to expire with this shared schedule.

-- Cleanup Cache DECLARE @ExpiredScheduleID UNIQUEIDENTIFIER DECLARE @ExpiredScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete' -- Get the Schedule identifier SET @ExpiredScheduleID = (SELECT ScheduleID FROM ReportServer_BENL_DWH_1.dbo.SCHEDULE WHERE NAME = @ExpiredScheduleName) -- pass the Schedule identifier to the Event insert procedure EXEC ReportServer_BENL_DWH_1.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ExpiredScheduleID -- Execute a Shared Schedule: DECLARE @ScheduleID UNIQUEIDENTIFIER DECLARE @ScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete' -- Get the Schedule identifier SET @ScheduleID = (SELECT ScheduleID FROM ReportServer_BENL_DWH_1.dbo.SCHEDULE WHERE NAME = @ScheduleName) -- pass the Schedule identifier to the Event insert procedure EXEC ReportServer_BENL_DWH_1.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ScheduleID

Step 5- Test

That’s it! Now you can test if it works by running the report. It can take a while to cache the report so be patient! You can see when the subscription has been run on this screen:

 

You can also check in the database if the result is cached and when it will expire (AbsoluteExpiration field).

SELECT C.Name, EC.* FROM ReportServerTempDB.dbo.ExecutionCache AS EC INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

[message_box title=”Important” color=”red”]Keep in mind that after setting “Cache a temporary copy of the report. Expire copy of report after a number of minutes” or using the shared schedule, it will expire after 9999 minutes and NOT when you re-run a NULL Delivery. I thought SSRS would cache the new results but it doesn’t!  SSRS will save a fingerprint of the parameter configuration in history vs the cached result (ParamsHash colum in the ExectutionCache Table). When you re-run the subscription, SSRS will see that the result is already in cache, also when there is new data. It’s very important to cleanup the cache first![/message_box]

Note: There is no SQL command to refresh the cache of a NULL Delivery Provider, you need to do this via a shared schedule to expire cache. There is a command to refresh a cache refresh plan but we do the caching with the subscription. You can also cleanup the cache with this command (all caching will be deleted, also snapshots):

DELETE ReportServer_TempDB.dbo.SnapshotData WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID FROM ReportServer_BENL_DWH_1.dbo.Catalog C INNER JOIN ReportServer_TempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID WHERE LEFT(C.Path,8) = '/DWH_NL/') DELETE ReportServer_TempDB.dbo.ExecutionCache WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID FROM ReportServer_BENL_DWH_1.dbo.Catalog C INNER JOIN ReportServer_TempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID WHERE LEFT(C.Path,8) = '/DWH_NL/')

You can also do some extra checks in the SSRS database so see the cache settings:

SELECT * FROM ReportServe.dbo.CachePolicy ExpireFlags: 1 = Expire after XXX minutes,  2 = USING a Shared Schedule

Check in the TemDB if the Report is cached:

SELECT * FROM ReportServerTempDB.dbo.ExecutionCache

Another very handy option is to runn a single NULL Delivery Subscription (or another subscription) with a T-SQL statement:

EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription', @EventData = 'E7F3A09D-8576-4EC7-A1DB-86E244E0A670'

Replace the UID with the UID of the subscriptionID from the dbo.Subscription table.

Update 12-nov-2013

Today, a client contacted me that SSRS was showing him wrong results. After doing investigation, I discovered that all ETL processes where loaded and the reporting cache was refreshed. What was going wrong? My ETL-package to refresh the cache looked like this:

I discovered this when I was looking in the execution log, the byte count for the NULL-delivery subscription was 0, this means that the report was run from Cache. So when the NULL delivery was activated, SSRS had a fingerprint of the parameters (so cache exists).

 

And here is the SQL statement which you can use to check if a NULL-Delivery Subscription has been run. This is an example for one report. Check the rowcount! If it’s 0, it means the report has been run from cache.

SELECT C.Name, EL.TimeStart, EL.TimeEnd,EL.* FROM dbo.ExecutionLogStorage AS EL INNER JOIN dbo.Catalog AS C ON EL.ReportID = C.ItemID WHERE TimeStart > '2013-11-12' AND C.Name = 'PS_ManagementRapportage Productiviteit'

I think SSRS needs some delay in processing caching and the expire cache option because I run this SQL from a stored procedure.  This is hard to test. My solution for now is to delete alle the cache, hard coded. This can be accomplished with this SQL . I only delete the cache from the DWH_NL folder in this example.

DELETE ReportServerTempDB.dbo.ExecutionCache WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID FROM ReportServer.dbo.Catalog C INNER JOIN ReportServerTempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID WHERE LEFT(C.Path,8) = '/DWH_NL/')

 

Update:

I added a delay between Expire Cache and the NULL Delivery share schedule:

-- RUN SHARED SCHEDULE: FOREXPIRECACHING TO ACTIVATE ALL NULL DELIVERY SUBSCRIPTIONS DECLARE @ExpiredScheduleID UNIQUEIDENTIFIER DECLARE @ExpiredScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete' -- Get the Schedule identifier SET @ExpiredScheduleID = (SELECT ScheduleID FROM ReportServer.dbo.SCHEDULE WHERE NAME = @ExpiredScheduleName) -- pass the Schedule identifier to the Event insert procedure EXEC ReportServer.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ExpiredScheduleID -- There can be an issue with caching if the expiration is not 100% complete. We set a WAIT -- command to know sure that all cache has been expired. --The format is hh:mi:ss.mmm. WAITFOR DELAY '00:02:00.000'; -- RUN SHARED SCHEDULE: FORCACHING TO ACTIVATE ALL NULL DELIVERY SUBSCRIPTIONS DECLARE @ScheduleID UNIQUEIDENTIFIER DECLARE @ScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete' SET @ScheduleID = (SELECT ScheduleID FROM ReportServer.dbo.SCHEDULE WHERE NAME = @ScheduleName) EXEC ReportServer.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ScheduleID

http://www.sqlblog.nl/dynamic-refresh-ssrs-cache-using-sql/

SSAS optimsiation

*	Tables	Many to many - Check tables are written optimally (no duplicates and < 1mill rows)
1	DSV	Remove any unnecessary tables
2	Roles	Phoenix role doesn't have permissions?
3	Dimension Usage	Manufacturer to Product	*Make sure all materialized
4	Dimension	Account - Attribute relationship	*Rigid, etc.
5	Dimension	Misc. Account - Attribute relationship
6	Dimension	Product - Attribute relationship
7	Dimension	Wholesaler - User Hierarchy?
8	Dimension	Attribute hierarchy enabled for all attributes	E.g. Not needed if in User Hierarchy
9	Dimension	Estimate row count for all attributes
10	Calculated script	Review all iff null and use is empty
11	Calculated script	Review all = make sure use object reference
12	Partitions	Create all partitions (5-20million rows)
13	Partitions	Specify slices for each partition
14	Aggregation	Initially set most common attributes used in group by
15	Aggregation	Switch on usage based optimisation and run all queries for reports - rebuild aggregations
16	SSIS Cache	Build cache warmer SSAS job to run most common top level queries after cube has been refreshed
17	Reports	Shell use security to get wholesaler role and territory - sub reports remove all parameters definitions, pass through from parent report and then use separate defined data source. Finally cache all sub reports run so will be instant	Can set up schedule for cache and clearing down

Javascript picking up XML file in HTML (Reporting Services)

This is a quick blog to walk through creating an HTML page which has a search functionality that get's auto populated via an XML file. In our case a reporting services rdl file.


1. Create the input component
<input id="textBox" type="text"
            style="width:250px; height:20px; text-align: center; font-family: Arial; font-size:11pt; color:#444444; vertical-align:text-top;" 
            onkeyup="srch(this.value)" onclick="clik(1)"
                value="PLACEHOLDER TEXT"   />

2. JavaScript to call report and display in search box

<script type="text/javascript">
    var xmlData = ""
    var newUrl = "";
    var Type = "";
    var searchArray = new Array();
    function loadXML(output) {
        if (window.XMLHttpRequest) {
            xhttp = new XMLHttpRequest();
        }
        else {
            xhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xhttp.onreadystatechange = function () {
            if (xhttp.readyState == 4) { output(xhttp.responseXML); }
        }
        xhttp.open("GET", "http://ReportServerReportConnection&rs:format=xml", false);
        xhttp.send();
        return xhttp.responseXML;
    }

    function init() {
        loadXML(function output(resp) { xmlData = resp.getElementsByTagName("Details") })
    }
    function srch(searchStr) {
        if (document.getElementById("textBox").value.length == 0) {
            document.getElementById("results").style.visibility = "hidden";
            return;
        }

        var Result1List = "Group 1: <br>";
        var Result2List = "Group 2: <br>";
        var baseLink = "ReportServer full path to report Displaying";
        var stndParams = "&rs%3AParameterLanguage=&rc%3AParameters=Collapsed&rc%3AToolbar=False";

        for (var i = 0; i < xmlData.length && k < 20; i++) {
     
            if (xmlData[i].attributes.length == 5) {
                var name = xmlData[i].attributes[2].childNodes[0].data; //Name of parameter displayed
                var id = xmlData[i].attributes[1].childNodes[0].data; //unique id passed to report
            }
            else {
                var name = xmlData[i].attributes[1].childNodes[0].data; //Name of parameter displayed
                var id = xmlData[i].attributes[0].childNodes[0].data; //unique id passed to report
            }

            if (name.toUpperCase().indexOf(searchStr.toUpperCase()) >= 0 && searchStr) {

//SET the Type based on id parameter to determine group
  if (id.indexOf("G1") > 0)
    Type = "Group 1";
  else if (id.indexOf("G2") > 0)
    Type = "Group 2";

                if (Type == "G1") {
                    Result1List = "" + Result1List + "<span onclick=\"updateFrame('" + baseLink + buildUrl(name, id, Type) + stndParams + "','" + name + "')\">" + name + "</span><br>";
                }
  else if (Type == "G2") {
                    Result2List = "" + Result2List + "<span onclick=\"updateFrame('" + baseLink + buildUrl(name, id, Type) + stndParams + "','" + name + "')\">" + name + "</span><br>";
                }
            }
        }
        //Output list
        document.getElementById("results").innerHTML = Result1List + "<Br>" + Result2List;
        document.getElementById("results").style.visibility = "visible";
    }

    function buildUrl(name, id, Type) {
        var inter = "";
        if (Type == "G1") {
            inter = '&rp%3aPARAMETER1=%5BDimension%5D.%5BATTRIBUTE%5D.%26%5B' + cleanChars(id) + '%5D&rp%3aPARAMETER2=' + selectedValUnit;
        }
        else if (Type == "G2") {
            inter = '&rp%3aPARAMETER1=%5BDimension%5D.%5BATTRIBUTE%5D.%26%5B' + cleanChars(id) + '%5D&rp%3aPARAMETER2=' + selectedValUnit;
        }
        return inter;
    }

    function updateFrame(link, name) {
        if (name == '') {
        }
        else {
            document.getElementById("textBox").value = name;
        }
        document.getElementById("results").style.visibility = "hidden";
        document.getElementById("reports").src = link;
       
    }

    function updateQueryStringParameter(uri, key, value) {
        var pos1 = uri.indexOf(key) + key.length + 6;
        var stringSelect = uri.substring(uri.indexOf(key), pos1);
        stringSelect = stringSelect.substring(stringSelect.indexOf("&"), 0);
        var urinew = uri.replace(stringSelect, key + "=" + value);
        return urinew;
    }

    function cleanChars(word) {
        var str = word;
        var pos1 = str.lastIndexOf("[");
        pos1 = pos1 + 1;
        var pos2 = str.lastIndexOf("]");
        word = str.substring(pos1, pos2);
        return word;
    }
    function clik(settextbox) {
     
      if (settextbox == 1) {
          document.getElementById("textBox").value = " ";
       }
    }
 
</script>

"Enable Visual Totals" in SSAS Security role

If you are filtering specific attribute members in SSAS and want the user to see the total of just the attribute members selected rather than all (even though they can only see the attributes you have allowed) then you must select "Enable Visual Totals".


If, at the all level, the user should see all total (including ones they can't see) then do not select the Enable Visual Totals.


If, at the all level, the user should only see the total for the members selected then select Enable Visual Totals.


E.g. Attribute A with 3 members with Measure X.


Attribute  | X Value
All           | 6     
M1           | 1     
M2           | 2      
M3           | 3        




If you grant the role access to M2 (2) and you do not have Enable Visual Totals select:


Attribute  | X Value
All            | 6          
M2           | 2           




If you grant the role access to M2 (2) and you have Enable Visual Totals select:


Attribute  | X Value
All           | 2         
M2           | 2      
          

Backing up Analysis Services

There's a very straight forward way to backup a cube onto a shared location.


First create the folder within the shared path e.g. \\SHAREDPATH\CUBEFOLDER


Open new XMLA window and  run XMLA code below on server with the cube



<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>CUBENAME<DatabaseID>

</Object>

<File>\\SHAREDPATH\CUBEFOLDER\CUBENAME.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>