Saklani, 15 November 2017
Many a times we come across the situation wherein we are trying to create
a subscription for a SSRS report, but it fails with a message as below.
Now, this problem looks like the report expects the user to provide credentials when it is viewed. When you create a subscription, it does not have the credentials to execute the report. SSRS requires a Login to connect to the datasource to process the report when subscription will be occurring at its scheduled time. It was required to store the credential securely into report server. There are two approaches basically for storing the credential securely into report server. Either you can change the datasource properties at the report level or at the datasource level itself.
I came across such a scenario recently.
I created a new datasource on my report manager and was able to make a
successful connection with the database. I created a SSRS report and pointed it
to my new datasource. The report ran good as well. But when I came to create
the subscription I got the error.
Here it was:
PPR0ACH 1:STORING THE CREDENTIALS AT THE REPORT LEVEL
If you are having this issue for any report follow the steps below:
Step 1: Go to Report -> Properties -> Click on the
Data Sources tab
Click on A custom data source and
choose option credentias stored securely in the report server and
provide credential information and further you can use option Use as Windows credentials when connecting
to the data source.
Step 3: Click on apply button.
Now you are good to create a subscription.
APPR0ACH 2:STORING THE CRENDENTIALS AT THE DATASOURCE LEVEL
We can even change the same at the datasource level.
Step 1: Open the datasource from the Data Sources at the report manager.
Step 2: Fill in the connection string
3:Choose to connect using: Credentials stored
securely in the report server. Put the credentials and use option Use as Windows credentials when connecting
to the data source.
Now can you can to the desired report and choose the option of ‘a shared
data source. You are good to go with creating a new subscription now.
·If you are worried about security, I would
suggest changing the datasource properites on the report level rather than the
data source level itself. (i.e approach 1 as mentioned above)
· If your report uses sub-reports also ensure
that the sub-reports are using the data source with saved credentials,
otherwise it won't let you create a subscription on the parent report.
INDEX idx_ci ON FactFinance(DateKey)
-- "Swap" the Clustered Index through a
Clustered ColumnStore Index
CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance WITH (DROP_EXISTING = ON)
You should have now sorted data in
the Clustered ColumnStore Index, and Segment Elimination should work quite
-- Segment Elimination works better
than previously, but still not perfectly
SELECT DateKey, SUM(Amount)
DateKey >= '20101229' AND DateKey <= '20131228'
But when you look again at the output
of STATISTICS IO, SQL Server still
has to read a lot of segments, and only skips a few of them:
But why can’t SQL Server skip all
segments other than the one we are interested in? The problem lies in the
creation of the Clustered ColumnStore Index. Execution Plan, the ColumnStore
Index Insert (Clustered) operator was running in parallel – across multiple
worker threads. And these worker threads are again destroying the order of your
data in the Clustered ColumnStore Index! You read your data pre-sorted from the
Clustered RowStore Index, and then the parallel creation of the Clustered
ColumnStore Index reshuffles your data… That hurts – again!
You can only solve that problem by
creating the Clustered ColumnStore Index with a MAXDOP of 1:
CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance
WITH (DROP_EXISTING = ON, MAXDOP = 1)
This sounds terrible, and it is
terrible!!! But it’s the only solution to let you preserve the ordering of your
data in the ColumnStore Index. When you later read from the Clustered
ColumnStore Index, you will then see that SQL Server was finally able to skip
all Segments other than the one you are interested in:
ClusteredColumnStore Indexes are great.
But by default, the Segment Elimination can’t be performed very well, because
you have no predefined sorting order in your Clustered ColumnStore. Therefore,
you should always make sure that the Segment Elimination works well, when you
tune your ColumnStore queries. And sometimes you even have to work against SQL
Server by using a MAXDOP 1 to preserve the ordering of your data.