dinsdag 26 juli 2011

Denali : Installing the SSAS Tabular Model project AdventureWorks

Introduction
I wanted to take a look at the new tabular model (BISM) and i've already read some things about BISM and the multidimensional mode on the blog of Chris Webb. These two are not working together. The BISM and multidimensional model are two different things. It seems that you need to install the "tabular mode" on a seperate instance. During the new installation of Denali CTP3 (post) i pressed next, next, etc and i didn't noticed a choice for this tabular mode (i've to read my own post in detail to make sure ;-)).

Installing a new instance in Denali
The first thing we have to do is installing a new instance on SQL Server. So start up the setup.exe again an let's go through the installation process again. I'm not showing every window again. You can read more about installing Denali in a former post.


Click on installation.


Press Next,  Next until you arrive in this window.


Press Next, Next until you arrive in this window.

First i thought that i had to enter information about the instance here. But creating a new instance is done later in the installation process.

Press a couple of times Next, Next, Next, etc until you arrive in this window.


etc, etc...


Press Next, Next, etc until you arrive in the next window:


blabla Next, etc.


Done......

Installing the Tabular model project
Okay now it's time to install the BISM project. If you download the examples from Microsoft and extract the files you will see the following files.


The following files are available in the folder "Adventure Works DW Tabular Denali CTP3".



Again, the following files are available in the folder "Adventure Works DW Tabular Denali CTP3".

The first file is the BIM file and the other one is the BIMproject file. The bin folder is empty. Okay lets double click on the projectfile and ....


The message appears : "You cannot open the BIM file. Reason: The workspace database server 'localhost' is not running in Vertipaq mode.". I immediately thought that this has something to do with the instance i've created. On the localhost i've installed the Multidimensional mode and on the BISM instance i've installed the tabular mode. I opened the properties of the project and noticed that the propery Workspace Server was "localhost". I changed this to localhost\bism.



After i changed the property, a verification process is started right away. It's telling me that the connection to server is succeeded. Great.


And yes it seems that we can run the project.


Okay now let's see how it looks in SQL Server Management Studio. I would have expected a choice for connecting to the BISM services or whatever.

So how do i see the deployed projects? Let's try this


Does that work? Yep it seems we have succeeded.

I've to make a remark. As you can see a project is deployed, here. Before i tried to connect to the bism server (?) i played a bit with Adventureworks project. I tried to deploy the project and i got an error:


I assumed that the deployment didn't succeed but looking in SSMS show a deployed project(?!). So is this a bug? Interesting. So i deleted the project in SSMS.


And the project was deleted from BISM server. I tried to deploy the project again, the error occurred again and..... the project is gone/not there! So i assume that the project was already installed during the installing process.



Now, i've to found why the adventureworks project don't install on the server anymore, but that is one for a future post.

Conclusion
One conclusion you can draw from this post is that MDX en BISM don't mix (duh). These two cannot installed on the same instance. So when you're developing mixed projects in MDX and BISM you need to install a seperate instance.

Next time we will go deeper into working with this BISM and the adventureworks example.

Greetz
Hennie

vrijdag 22 juli 2011

Denali : 10 situations with the EOMONTH() function.

Hi,

In Denali is a new function is available: EOMONTH() and it returns the last day of the month of specified date, with an optional offset. More information can be found on technet. Return type is the type of start_date or datetime2(7). EOMONTH() is the same name as used in Excel.

So i tried some things with this function. Below you can see the results:


Situation 1

SELECT EOMONTH(getdate())

Returning:




Always nice to try

SELECT EOMONTH('2012-02-12')
 

Returning:
Situation 2

Yup that works too!


Situation 3
Now lets try somethiing with the offset.

SELECT EOMONTH('2013-02-12', -10000)

Returning:



Thats a lot of years back..


Situation 4
Now let's try to crash the EOMONTH() function.

SELECT
EOMONTH('2013-02-12', -100000)

Returning:

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime2' column caused an overflow.


Situation 5
Let't try some implicit conversion of the function

  SELECT EOMONTH('20110721')

Returning




Situation 6
Let's try an  impossible implicit conversion

SELECT EOMONTH('20110750')

Returning

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Situation 7
Let's try a another  impossible implicit conversion
SELECT  EOMONTH(20110750)
 

Returning

Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of eomonth function.



 
Situation 8
BOMonth()?

SELECT BOMONTH = EOMONTH(getDate(), -1) + 1
 

Returning



Situation 9
SELECT *
FROM dbo.FactResellerSales RS
INNER JOIN DimDate D ON RS.OrderDateKey = D.DateKey
WHERE D.FullDateAlternateKey Between CAST('20040501' AS DATETIME) AND EOMONTH('20040501')


Returning







Situation 10
Hmm the last one... Can't think of a new situation anymore. I'm out of inspiration! I suggest that you think about the tenth situation and let me know what you've came up with....

Conclusion
EOMONTH() is an interesting function but as other bloggers suggests, why is the only new Date function that is released. There are so many datefunctions to think about...

Other interesting functions to discover are:

  •     IIF
  •     CHOOSE
  •     CONCAT
  •     DATEFROMPARTS
  •     TIMEFROMPARTS
  •     DATETIME2FROMPARTS
  •     DATETIMEFROMPARTS
  •     SMALLDATETIMEFROMPARTS
  •     TRY_CONVERT
  •     FORMAT

This is not a complete list.
Very interesting link is on beyond relational : http://beyondrelational.com/whatisnew/sqlserver/denali/ where you can find all the features released in Denali.

Greetz,
Hennie

woensdag 20 juli 2011

Installing SQL Server Denali CTP3 Walkthrough

Introduction
In case you missed it: SQL Server Denali CTP3 is out! At last a new CTP version of Denali and this time (relative) more features available than in CTP1. CTP2 was not widely distributed, but only for a happy few. This post is about installing the x86 version on my virtual machine. Created a new clean VM environment ready for installing Denali CTP3. Below i've gathered the most interesting links about CTP3 (so far for this moment):
Downloading the necessary software of Denali CTP3
First downoad the following software:
  1. Denali CTP3.
  2. Sample databases (future post).
  3. Feature pack.

Installing Denali CTP3
So the first thing to do is downloading the CTP3 version. After installing the download manager you can download the CTP3 version:


The first thing i did is double clicking on the application file : SQLFULL_x86_ENU_INSTALL and the next thing that happens is the "Unloading the Box" window that opens.


There seems some unzipping taking place here in a folder SQLFULL_x86_ENU and i waited a couple of minutes before it's completed. the following files are available in the SQLFULL_x86_ENU folder:


Double clicking the setup.exe did the following window showing up:


And then we're in the installation center:


 And we leave the first tab as it is and go to the second option : "installation"


and choose "New SQL Server stand-alone installation or add features to an existing installation" and it seems that i've passed some potential problems:


Press ok and the following window appears. There is only an evaluation version , yet ;-)


Press next.

Press Next.



Press install. I'm ignoring the windows firewall problem.


Press Next. Just a simple installation is needed.


Press Next. Now we can select the features that are available and i choose the full option model. Not sure whether we run into problems but we'll see.


Press Next..... and....an error has occurred: Powershell 2.0 not installed. Hmmm....


Strange error: "Windows Powershell 2.0 already installed" ? Below the error message in more detail. "Do not select the feature that require PowerShell 2.0"? How should i know which feature is using powershell 2.0?


I've downloaded powershell 2.0 from MSDN, installed and rerun the installation process again. Now, it seems that i've passed the powershell 2.0 prerequisites. I've noticed that before the installation rule window a message already appeared about installing powershell 2.0 (right bottom - two windows back).


Press Next. It seems there is still enough space available on my C drive.

Press Next.


Press Next. Adding current user. Not a very best practice but i'm just want to run Denali as quick as possible.


Press Next.


Press Next.

Press Next.

Press Next.


Press Next.

Press Next.


Press Next.


Press Next and in the installation begins


Seems that everything is succeeded


And finally execute a reboot and start SQL Server Management Studio via the Menu Start. You can see the following entries inthis menu:


I'm not sure whether i've installed all these menu entries during the installation of Denali CTP3. I did have a clean windows install (Windows Server 2008) but the yellow entries are new and the white entries should be older. Not quite clear why some are white and some are yellow.

When i start up the SQL Server Management Studio the following window appears:


And looking in the Object explorers of SSMS, SSIS (Control and dataflow) and SSRS shows me:


Well this is it for now.

Conclusion
The basic installation went smooth and i had one small problem with Powershell 2.0 that was a prerequisite (and wasn't reported during the analysisin the setup support rules window). I'm particularly interested in DQS and in SSIS deployment model. I hope to find out more in future about these parts.

Greetz,
Hennie