Step 2. Create another table (Table 3) with the same schema
as the existing table Table1. But base
this on the partition scheme rather than on filegroup.

For this I scripted the create table script from Table 1 as
this will all the constraints of the existing table. But I changed the On filegroup statement to
On yearly_ps

Step 3. Create a
staging table (Table 2) to insert data from Table 1 on a year by year basis.

For this I used the create table script from Table 1

Step 4. Insert the
data from the staging table with a where clause that includes yearly data into
Table 3.

Step 5. After all the
data is inserted into Table 3, rename Table 1 an rename Table 3 to Table
1. When you are satisfied with
everything, then drop Table 1.

I did this process from 2007 to 2012.

After that I started wondering do we really need the staging
table.

Since this is a dev environment, I thought I will try
without the staging table.

So I repeated the steps 1 to 2 above after dropping Table 3. Then I started to Insert data straight into
Table 3 on a yearly basis. I checked
every time whether the yearly data is going to the correct partition or not.

The script I used for this is as follows.

SELECTOBJECT_SCHEMA_NAME(p.object_id) AS SchemaName
, OBJECT_NAME(p.object_id) AS ObjectName
, i.name AS IndexName
, p.index_id AS IndexID
, ds.name AS PartitionScheme
, p.partition_number AS PartitionNumber
, COALESCE(fg.name, fg2.name) AS FileGroupName
, prv_left.value AS LowerBoundaryValue
, prv_right.value AS UpperBoundaryValue
, CASE ISNULL(CAST(pf.boundary_value_on_right as SMALLINT),-1)
WHEN 1 THEN 'RIGHT'
WHEN -1 THEN 'N/A' ELSE 'LEFT' END AS PartitionFunctionRange
, p.rows AS Rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
LEFT JOIN sys.data_spaces dsp
ON ds.data_space_id = dsp.data_space_id
LEFT JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.filegroups AS fg2
ON fg2.data_space_id = dsp.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
where OBJECT_NAME(p.object_id) = 'Table3'
)
ORDER BY ObjectName, IndexId, PartitionNUmber ;

The process was smooth.

I would like to implement this in Production. I would like some feedback of the viewers of
this blog as to whether I am doing any blunders here.

These are the steps I followed to export into two different
tabs of an excel sheet.

Step 1 Right click on
a column in the first Tablix and choose Tablix Properties.

On the General tab there are Page
Break options.

Choose the Add Page Break After
option as shown below.

Step 2 Next step is
to ensure that the header is repeated on the second tab. To achieve this,
select the header and choose properties.

Go the RepeatWith
Property and choose Tablix2 as shown below.

Now when you export data using Export to Excel – there will
be two sheets populated. The first sheet
will contain the first Tablix information and the second sheet will contain the
second Tablix information.

Monday, January 15, 2018

From the past 3 days I have been working on resolving merged
and hidden cells issues when an SSRS reports is exported to excel.

Here are some tips that you can use to resolve this issue.

To eliminate blank A,B,C columns at the beginning of the excel file,
ensure that the report starts with no space on the left hand side of the report
as shown below.

When you have multiple Tablix in your report ensure that
they are aligned on both left and right sides of the report as shown below.

When you have title blocks ensure that they are in line with
all the Tablix of the report as shown below

Last but not least if you have column groups, then ensure
that the report size is same as all the Tablix and titles aligned as shown
below.

After following all these tips and there are still merged
and hidden cells, then ensure that each of the cells/columns in your matrix or Tablix
are formatted with width rounded off to either no decimals or 2 decimals as
shown below.

Hope you will get rid of the merged and hidden cells problem a lot quicker than the time I spent resolving this issue.

Step 2 Once the Edit Interactions button is highlighted, you
can see extra icons for filter and none in the visuals when you hover over the
top of the visual as shown below. By
default the filter icon is highlighted.

Step 3 To disconnect
the visual from the filters click on the none icon as shown below. This will not change the behavior of the
visual based on the filter selected.

By doing this even if we change the filter value for Billing Month, the visual displays all the 18 months.

Thursday, January 04, 2018

Did you know that you can connect to your Power BI Desktop
Model from Sql Server Management Studio (SSMS)?

If not, this blog post is for you.

In this blog post I will be showing you how to connect to
your Power BI Model that you have opened with Power BI Desktop from SSMS.

Whenever I try to Get Data in Power BI, I always closely
monitor the Task Manager for Memory usage.

I always notice that there is the Microsoft Sql Server
Analysis /services and Power Bi Desktop applications top the memory usage as
shown below.

What this means is Power BI Desktop uses Sql Server Analysis
Services for getting data into its memory. Even though your local system does
not have SSAS installed, it uses the msmdsrv executable that’s in the bin folder
of the Power BI Desktop folder as shown below:

So there is a close connection between Power BI Desktop and
SSAS.

Now coming to the actual content of this blogpost – In order
to connect to the Power BI Desktop model from SSMs, you need to know the exact port that the local instance of SSAS is
running. To find out this port, follow
the below steps –

Step 1 Open the
Power BI Desktop model you are trying to access from SSMS using the Power BI
Desktop application (October 2017). I used the Power BI Desktop application optimized for
Power BI Report Server.