Random Data Using SQL Default Values

Wow, I decided to take a bit of time off over the winter and the next thing I know it's been two months since the last  SQL Banana update!

While faffing around over the last few weeks I came across a few code snippets I found quite useful.  Here's the first :)

Often I need to fill a table with some random dummy data.  Lot's of people have different ways of doing this, and I don't want to get into a discussion of what "random" actually means when working with computers, but 99% of the time this approach works just fine for me.

First create your table.  Set a default constraint to use the NEWID() function - this will generate a uniqueidentifier value.  The value will be unique every time it is generated.  Then use the CHECKSUM() function around it to compute it's hash value,  for our purposes effectively generating an int based on the uniqueidentifier value.

CREATE TABLE dbo.Dummy
(
Id INT IDENTITY (1,1) PRIMARY KEY,
IntData INT CONSTRAINT DummyDefault DEFAULT CHECKSUM(NEWID()))

Next, insert as much row data as required:

INSERT INTO dbo.Dummy DEFAULT VALUES
GO 500

This will run the insert 500 times, each time the identity column will increment, and a random integer will be inserted into the IntData field.

If you require varchar data then simply adjust the default constraint as required. EG:

CharData VARCHAR(MAX) CONSTRAINT TestCharDefault DEFAULT CAST(NEWID() as VARCHAR(MAX)) 

And that's it. Dummy data is now ready for use.

SSAS: Setting the Default Measure Using MDX

I recently used a nifty bit of code to set the default measure for a cube using MDX. 

The most obvious way of setting the default measure for a cube using is relatively easy.  In the cube editor, under the Cube Structure tab, highlight your cube (at the top of the measure group tree). In the properties on the right of the screen (shortcut F4) there is a property called DefaultMeasure.  Click the dropdown and select a measure from the list.

 On closer examination, however, none of the measures in the drop down are calculated measures. So what if you want the default measure to be just that?  The answer is that the default measure can be set using MDX in the Calculations tab of the cube editor.  The code do it is as follows:


ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].MyDefaultMeasure;

That's all there is to it. Put whatever measure you like here, calculated or not. 

Additionally, if you now want to change the default measure to be a different one, that would previously have required changing the DefaultMember property mentioned earlier and then redeploying the entire cube (using the Deployment Wizard, direct from BIDS etc), which can have other consequences.  However, by assigning the default measure in the calculations tab it has the added benefit that making a change only requires deployment of the MDX script and not the rest of the database. Using a tool like BIDS Helper (from codeplex) means this can be done in a single click from the Calculations tab with no impact to the rest of the cube design.