WPF DataGridGrouping

For those of you that do not have access to some of the better .Net Grid controls here is a simple overview on the WPFToolKit DataGrid Grouping. For some reason this took me a little longer to figure than I thought it should have.

We have three primary components to make the grouping happen. Style, GroupStyle and ListCollectionView.

First is the Window.Resource or Style:
 This should be placed above the Grid node in your window. This will create the Style that will hold the Grouped Date.




Next we need GroupStyle in the actual DataGrid:
 Place this below the DataGrid Properties and above the DataGrid Columns



Finally you need to load a new GroupDescription with a ListCollectionView
The below method is called on load and the ListCollectionView allows you to have a GroupDescription.Add which sets up your DataGridGroup
.


Example:

In my finally example, I have added an expander that allows me to expand and collapse on the grouped field in the DataGrid. Seems like a pretty simple task but for some reason I had a hard time figuring this one out. 

Posted Code Sample:
WPFCRUDGrouping

SSAS - Setting Attributes as Properties for Excel


Comments (6)

Forward to a friend

Print

Rating: 3/5 1 Votes

change text size: A A A

Published 12/8/2010 03:31 PM by MikeDavis

When a user is browsing your cube with excel they may not understand how to slice the data and end

up using things like “first name” as a hierarchy to slice the data. This does not make for good analysis,

since grouping all of the people with the same name would not be useful (Unless your business is in

analyzing names).

So a better practice is to set the “Attribute Hierarchy Enabled” property of the attributes to false, for

attributes you don’t want the user slicing on, like First name, Last name, Email Address, and phone

number. Then the user can see the properties in excel without having to do any slicing.

Below are examples using the employee dimension in Adventure Works. You can ignore the parent

child relationship on the dimension.

Here is an example of what NOT to do:

In the below image you can see all of the attributes of the dimension are available and there are no

hierarchies built. So if an end user wanted to slice by marital status and see the first and last names of

the people in the different statuses, they would have to slice by all three of these attributes.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

1 of 7 4/6/2011 6:42 PM

In Excel it would look like this:

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

2 of 7 4/6/2011 6:42 PM

This is confusing and is very bad for query performance.

To fix this you will need to build a hierarchy and let the user see the properties of the employee.

To do this:

1. Drag marital status into the hierarchies window in the center of the dimension editor

2. Drop the employee key under martial status

3. Rename the hierarchy to Marital Hierarchy

Now you can disable the attributes you don’t want the user slicing on like Phone, Login ID, Last name,

First Name, and Email Address.

To do this:

1. Hold CTRL and click on each of the attributes listed above

2. In the properties window change the Attribute Hierarchy Enable Property to false

The Dimension Structure should look like the image below.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

3 of 7 4/6/2011 6:42 PM

Click on the Attribute relationships tab and it should look like the below image.

Notice the Grey next to the disabled attributes in the bottom window. Now process and deploy the

dimension and the cube.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

4 of 7 4/6/2011 6:42 PM

The user will only need to slice by the Marital Hierarchy in Excel as seen in the image below.

The user can slice the information by marital status and then right click to see the properties of the

employee as seen in the image below.

Note: The employee Key is showing in this example instead of the login ID because the name property

of the employee key was changed to the login Id.

The end user can now get the properties of each of the employees by right clicking on the employee

and selecting “Show Properties in Report” and selecting the information they want to see.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

5 of 7 4/6/2011 6:42 PM

Now selecting the first and last names do not slice the cube by the names and just show the names on

the report as seen in the below image.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

6 of 7 4/6/2011 6:42 PM

This should make your queries faster and allow your users to see the information they want without

doing and lot of extra slicing.

To give you a deeper understanding of what is occurring here, imagine that you have millions of rows of

data on your fact table and you drag out first name like in the example above of what NOT to do. This

divides all of the data into groups by the first names. So anyone with the name Mike would be grouped

together. Grouping the Mikes together is not useful. The only reason the user is doing this is because

they want to see the name once they get down to the employee level.

By changing this behavior in your user and teaching them how to get the properties, your SSAS queries

will run much smoother and give your users a better experience.

Print http://www.bidn.com/articles/ssas-development/191/ssas-setting-attribute...

7 of 7 4/6/2011 6:42 PM

2012 MS SQL TSQL Functions

-- Format very useful in formatting results

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy'),

          FORMAT(1.0, 'C'),

          FORMAT(42, '###,##0.00')

-- turn text into a particular data type, if it can’t be converted then it fails

select parse('05/15/1968' AS datetime2),

              parse('$123.45' AS money)

-- same as parse but if it doesn’t convert it returns null

SELECT try_parse('05/15/1968' AS datetime2),

              try_parse('99/15/1968' AS datetime2)

-- evalues the first parameter, then returns the second if true, or the third if false.

SELECT IIF(1=1, 'true', 'false'),

       IIF(try_parse('99/15/1968' AS datetime2) is NULL, 'Was Null', 'Not Null')

-- indexed based, conversion if 1 then ‘one’, if 2 then ‘two’ etc.

SELECT CHOOSE(1, 'One', 'Two', 'Three')

-- concat function, not sure of usefulness yet maybe in conjunction with format to make some nice strings, but sadly no aggregate version

-- there are a couple funky rules about size of strings so read up before assuming.

SELECT CONCAT('first string', ' Second String')

-- return the end of month calendar date for any given date, can also use an offset, with offset it adds X months to the date then calculates the end of month

SELECT EOMONTH('5/15/1968'), EOMONTH('5/15/1968', 3)

-- create a date object from the individual values.

SELECT DATEFROMPARTS(1968, 5, 15),

              DATETIME2FROMPARTS(1968, 5, 15, 4, 0, 30, 0, 0)

SQL 2012 SSRS Sharepoint 2012 Integration Corrupt Data Driven Subscriptions

      We recently upgraded from SQL SSRS 2008 R2 Sharepoint 2010 Integration Mode to  SQL SSRS 2010 Sharepoint 2010 Integration Mode. After making this upgrade we were no longer able to edit any of the Data Driven subscriptions. The reason for this is that SSRS 2010 Data Driven subscriptions require a few additional nodes than 2008 R2. If you edit the subscriptions in the ReportServer.dbo.Subscriptions table you will be able to edit via the SharePoint/ReportServer UI. 

F    Follow the below steps and script.

1.       Find the SubscriptionID

2.       Update the SubscriptionID variable

3.       Run the Subscription update

4.       Edit the Subscription in SharePoint to confirm

Due to formatting I had to upload the script in a file:
http://fsugeiger.com/misc/codesamples/sqlssrssubscriptionfix.txt

SQL Server Index and Statistics Maintenance

Just attended Orlando.Net Code Camp this past weekend and set in on a session from Rob Biddle a Data Architect where he had a recommended a Ola Hallengren index and stats maintenance process. Just wrapping up testing on the dev box now and slowly folding them into my production maintenance routine. Something you should check out.

Resource Link:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics. IndexOptimize is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts.

License

The SQL Server Maintenance Solution is free.

Support

Request professional support or custom modifications.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
SYSTEM_DATABASES	All system databases (master, msdb, and model)
USER_DATABASES	All user databases
ALL_DATABASES	All databases
Db1	The database Db1
Db1, Db2	The databases Db1 and Db2
USER_DATABASES, -Db1	All user databases, except Db1
%Db%	All databases that have “Db” in the name
%Db%, -Db1	All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db%	All databases that do not have “Db” in the name

FragmentationLow

Specify index maintenance operations to be performed on a low-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
NULL	Do not perform index maintenance. This is the default for a low-fragmented index.

An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.
IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

FragmentationMedium

Specify index maintenance operations to be performed on a medium-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index. This is the default for a medium-fragmented index.
NULL	Do not perform index maintenance.

An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.
IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

FragmentationHigh

Specify index maintenance operations to be performed on a high-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index. This is the default for a high-fragmented index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
NULL	Do not perform index maintenance.

An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.
IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

FragmentationLevel1

Set the lower limit, as a percentage, for medium fragmentation. The default is 5 percent. This is based on Microsoft’s recommendation in Books Online.
IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

FragmentationLevel2

Set the lower limit, as a percentage, for high fragmentation. The default is 30 percent. This is based on Microsoft’s recommendation in Books Online.
IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

PageCountLevel

Set a size, in pages; indexes with fewer pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation in this white paper.
IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

SortInTempdb

Use tempdb for sort operations when rebuilding indexes.

Value	Description
Y	Use tempdb for sort operations when rebuilding indexes.
N	Do not use tempdb for sort operations when rebuilding indexes. This is the default.

The SortInTempdb option in IndexOptimize uses the SORT_IN_TEMPDB option in the SQL Server ALTER INDEX command.

MaxDOP

Specify the number of CPUs to use when rebuilding indexes. If this number is not specified, the global maximum degree of parallelism is used.
The MaxDOP option in IndexOptimize uses the MAXDOP option in the SQL Server ALTER INDEX command.

FillFactor

Indicate, as a percentage, how full the pages should be made when rebuilding indexes. If a percentage is not specified, the fill factor in sys.indexes is used.
The FillFactor option in IndexOptimize uses the FILLFACTOR option in the SQL Server ALTER INDEX command.

PadIndex

Apply the percentage of free space that the FillFactor parameter specifies to the intermediate-level pages of the index.

Value	Description
Y	Apply the percentage of free space that the FillFactor parameter specifies to the intermediate-level pages of the index.
N	The intermediate-level pages of the index are filled to near capacity. This is the default.

The PadIndex option in IndexOptimize uses the PADINDEX option in the SQL Server ALTER INDEX command.

LOBCompaction

Compact pages that contain large object (LOB) columns, when reorganizing indexes.

Value	Description
Y	Compact pages that contain LOB columns, when reorganizing indexes. This is the default.
N	Do not compact pages that contain LOB columns, when reorganizing indexes.

The LOBCompaction option in IndexOptimize uses the LOB_COMPACTION option in the SQL Server ALTER INDEX command.

UpdateStatistics

Update statistics.

Value	Description
ALL	Update index and column statistics.
INDEX	Update index statistics.
COLUMNS	Update column statistics.
NULL	Do not perform statistics maintenance. This is the default.

IndexOptimize uses the SQL Server UPDATE STATISTICS command to update statistics.

StatisticsSample

Indicate, as a percentage, how much of a table is gathered when updating statistics. A value of 100 is equivalent to a full scan. If no value is specified, then SQL Server automatically computes the required sample.
The StatisticsSample option in IndexOptimize uses the SAMPLE and FULLSCAN options in the SQL Server UPDATE STATISTICS command.

StatisticsResample

Update statistics with the most recent sample.

Value	Description
Y	Update statistics with the most recent sample.
N	Let SQL Server automatically compute the required sample. This is the default.

The StatisticsResample option in IndexOptimize uses the RESAMPLE option in the SQL Server UPDATE STATISTICS command.
You cannot combine the options StatisticsSample and StatisticsResample.

OnlyModifiedStatistics

Update statistics only if any rows have been modified since the most recent statistics update.

Value	Description
Y	Update statistics only if any rows have been modified since the most recent statistics update.
N	Update statistics regardless of whether any rows have been modified.

IndexOptimize checks rowmodctr in sys.sysindexes to determine whether any rows have been modified since the most recent statistics update.

PartitionLevel

Maintain partitioned indexes on the partition level. If this parameter is set to Y, the fragmentation level and page count is checked for each partition. The appropriate index maintenance (rebuild or reorganize) is then performed for each partition.

Value	Description
Y	Maintain partitioned indexes on the partition level.
N	Maintain partitioned indexes on the index level. This is the default.

TimeLimit

Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

Indexes

Select indexes. If this parameter is not specified, all indexes are selected. The ALL_INDEXES keyword is supported. The hyphen character (-) is used to exclude indexes, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

Value	Description
ALL_INDEXES	All indexes
Db1.Schema1.Tbl1.Idx1	The index Idx1 on the object Schema1.Tbl1 in the database Db1
Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2	The index Idx1 on the object Schema1.Tbl1 in the database Db1 and the index Idx2 on the object Schema2.Tbl2 in the database Db2
Db1.Schema1.Tbl1	All indexes on the object Schema1.Tbl1 in the database Db1
Db1.Schema1.Tbl1, Db2.Schema2.Tbl2	All indexes on the object Schema1.Tbl1 in the database Db1 and all indexes on the object Schema2.Tbl2 in the database Db2
Db1.Schema1.%	All indexes in the schema Schema1 in the database Db1
%.Schema1.%	All indexes in the schema Schema1 in all databases
ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1	All indexes except the index Idx1 on the object Schema1.Tbl1 in the database Db1
ALL_INDEXES, -Db1.Schema1.Tbl1	All indexes except indexes on the object Schema1.Tbl1 in the database Db1

Delay

Set the delay, in seconds, between index commands. By default, there is no delay.

LogToTable

Log commands to the table dbo.CommandLog.

Value	Description
Y	Log commands to the table.
N	Do not log commands to the table. This is the default.

Execute

Execute commands. By default, the commands are executed normally. If this parameter is set to N, then the commands are printed only.

Value	Description
Y	Execute commands. This is the default.
N	Only print commands.

Examples

A. Rebuild or reorganize indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Rebuild or reorganize indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

C. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Execution

Execute the stored procedures from SQL Server Agent CmdExec jobs, using sqlcmd and the -b option. Pass the parameters to the stored procedures by name:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES'" -b
Use SQL Server Agent job output files with tokens for jobid, stepid, date, and time. Output files ensure that you always have a full set of information, in case of an error. Tokens ensure that the output files have unique file names.
You can use the MaintenanceSolution.sql script to install the SQL Server Maintenance Solution. Then the sqlcmd commands and the output files with tokens will be configured for you.

Getting Started

1. Download MaintenanceSolution.sql.
2. Execute MaintenanceSolution.sql. This script creates all the objects and jobs that you need.
3. Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Check the output files in the error log directory.
4. Schedule the jobs.

Upgrading

1. Download MaintenanceSolution.sql.
2. Change this line in the script so as not to create jobs:
   SET @CreateJobs = 'N'
3. Execute MaintenanceSolution.sql. The script drops the old versions of the stored procedures and functions and creates new versions.
4. Start the jobs and verify that they are completing successfully.