CREATE SUBCUBE in Microsoft Visual Studio for SQL2005

Hi,

I am trying to create a SUBCUBE in Microsoft Visual Studio (Analysis Services) but

It keeps on giving me an error when trying to build the Subcube.

The MDX statement is correct because I have tested it in SQL2005 Management Studio and it works.

My question is that “am I doing it in the right place?”:

This is under the Cubes Folder > Calculations Tab> New Script Command

My understanding is that under the “New Script Command” I can type any MDX statement and it should work? Or should this be done in another place?

When processing the cube the error I am getting back is:

Parser: The script contains the statement, which is not allowed

What I am trying to do is to create a slice of a cube by e.g. Brand which one of my departments need to use and should not see the other Brands. All the other dimensions and measures should still apply.

I would appreciate it if someone can please assist me with this.

Thank you in advance

Pieter Nelson

im no expert in olap cubes and am only tinkering with them for the last few months, but ive never heard of sub cubes. in that suituation, id either use a front end to create a report that filters out all but the brand your interested in, or create a seperate cube, based on the exact same data source view, but just import the brand you require. there may be a more elegant way, but those ways are pretty simple.

Regards,

Winston.

|||

Hi Pieter,

It sounds like you need to be using dimension security, not subcubes here. See
http://msdn2.microsoft.com/en-us/library/ms175366.aspx

for details on how to do this.

HTH,

Chris

|||

Unfortuanately I can not use Security or Another Measure Group.

I went into the Calculations Tab in Visual Studio and edited the Calculate Script that look like this:

-

CALCULATE;

CREATE SUBCUBE [Cube_AcqMgmtGF_Mthly] AS SELECT

{[All Brands].[All Brands].[Brand].&[Goldfishka B]} ON 0

FROM [Cube_AcqMgmtGF_Mthly];

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ave first Purchase]

AS '[Measures].[First Purch]/[Measures].[Usaccountno]',

FORMAT_STRING = "Currency",

VISIBLE = 1;

-

I am still trying to filter this cube to one specific brand, but the deployment keeps on failing with the following Description:

"An MDX Statement was expected. An MDX expression was specified" "The script contains the statement, which is not allowed"

To be honest here, I am now getting really confused.

SUMMARY:

Is my code in the right place "Create Subcube" or is there an alternative way of doing this.

PLEASE HELP ANYONE?

|||

Hi Pieter,

To be honest, I can't see anything wrong with your MDX (which service pack are you runnning?) but even if you could create a subcube in your MDX Script I don't think it's going to do what you want - even though I'm able to deploy a CREATE SUBCUBE command in an MDX Script it doesn't look like it does anything useful.

Why can't you use security or another measure group? Can you explain what you want to do in more detail?

Chris

|||

Hi,

I am trying to create subcubes(by Brands) from a Master cube (by only processing one cube), but the situation is that if I create a cube for every brand then it will be different processing for every cube. Unfortuanately these fact tables contains millions of records and processing all these records for every cube will take much longer. The other problem is that if there is changes in the logic of one fact table then I will have to apply these changes to all the other cubes individually.

Hope this helps?

|||

This sounds exactly what dimension security is intended for. Why do you say you can't use it?

Chris

Create Script Database

Hi, i want to know if there is a tool like scptxfr in SQL2005, in order to generate a script of all database.

I need this , because i have to make a job that automatically generates script of all database every day for backup.

Thks.

There is no such tool with SQL Server 2005. However you could create such a tool very with SMO with not much effort, depeding on what you want to do:

http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx

Create Maintenance Plans on SQL2K and SQL2005 w/SQL Mgmt. Studio?

I don't see any way to run the Maintenance Plan wizard against my existing SQL2K or SQL2005 DBs using the MS SQL Maintenance Studio. I read in another thread about having to install SSIS, and I think I have that on the SQL2005, but how is it done against the SQL2K DBs? I have dug around in the Mgmt Studio to no avail. If this thing is not fully backward compatible, then in addition to being slow as an old dog on a 2.5Ghz computer, then I really think MS has not demonstrated how cool this .NET stuff can be (or have they). What a sink for CPU cycles.

Do you have SQL 2000 server tools installed as a seperate instance, if so why not schedule with this server instance.

True that you have to use SSIS in order to run the maintenance plan and schedule that a seperate job, refer to the books online for more information in this regard.

|||

Yes, I do have the SQL 2000 Enterprise Manager installed, but I was trying to transition to a single set of tools to manage all my servers. One would think that MS would support fully the next most recent version.

What would be the way to verify if the SSIS is installed on a specific server?

Create database using dmo and sql2005

The following code which worked just fine with sql2000 sp3 no longer
works with sql 2005. The error I receive says: "The file filename.mdf
is compressed but does not reside in a read only database or filegroup.
The file must be decompressed".
This happens on the line of code where I wrote <--PROBLEM.
Can anyone help?
HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
databaseDirectoryLocation,CString& error)
{
HRESULT hr = NULL;
LPSQLDMODBFILE pMDFFile = NULL;
LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
LPSQLDMODATABASE pDatabase = NULL;
LPSQLDMOFILEGROUPS pFileGroups = NULL;
LPSQLDMOFILEGROUP pFileGroup = NULL;
LPSQLDMODBFILES pDBFiles = NULL;
LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
LPSQLDMOLOGFILES pLogFiles = NULL;
LPSQLDMODATABASES pDatabases = NULL;
CString fileName = dataBaseName + _T(".mdf");
error = _T("");
hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
if (hr == S_OK)
{
fileName = dataBaseName + _T(".ldf");
hr =
createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
if (hr == S_OK)
{
hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
if (hr == S_OK)
{
pDatabase->SetName(dataBaseName);
// add DBFile to DBFiles collection, in PRIMARY FileGroup
hr = pDatabase->GetFileGroups(&pFileGroups);
hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
hr = pFileGroup->GetDBFiles(&pDBFiles);
if FAILED( hr = pDBFiles->Add(pMDFFile) )
{
error = getDMOError(hr);
}
else
{
hr =
pDatabase->GetTransactionLog(&pTransactionLog);
hr = pTransactionLog->GetLogFiles(&pLogFiles);
if FAILED( hr = pLogFiles->Add(pLDFFile) )
{
error = getDMOError(hr);
}
else
{
// add Database to Database collection, this creates the data and
log
//devices and the actual new database
hr = pSQLServer->GetDatabases(&pDatabases);
if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
{
error = getDMOError(hr);
}
}
}
}
else
{
error = _T("CoCreateInstance failed in CreateDataBase.");
hr = S_FALSE;
}
}
else
{
hr = S_FALSE;
}
}
else
{
hr = S_FALSE;
}
// release all objects
if (pFileGroups) pFileGroups->Release();
if (pFileGroup)pFileGroup->Release();
if (pDBFiles) pDBFiles->Release();
if (pMDFFile) pMDFFile->Release();
if (pTransactionLog) pTransactionLog->Release();
if (pLogFiles) pLogFiles->Release();
if (pLDFFile) pLDFFile->Release();
if (pDatabases) pDatabases->Release();
if (pDatabase) pDatabase->Release();
return hr;
}
////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////
HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
{
HRESULT hr;
error = _T("");
try
{
hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
if (hr == S_OK)
{
pDBFile->SetName(fileName);
pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
pDBFile->SetSize(10); //MB
pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
pDBFile->SetFileGrowth(10);
pDBFile->SetMaximumSize(100);
pDBFile->SetPrimaryFile(TRUE);
}
else
{
error = _T("CoCreateInstance failed in createDBFile");
}
}
catch (...)
{
}
return hr;
}
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////
HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
{
error = _T("");
HRESULT hr;
try
{
hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
if(hr == S_OK)
{
pLDFFile->SetName(logFileName);
pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
pLDFFile->SetSize(5);
pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
pLDFFile->SetFileGrowth(10);
pLDFFile->SetMaximumSize(100);
}
else
{
error = _T("CoCreateInstance Failed in createLogFile");
}
}
catch (...)
{
}
return hr;
}Is the drve or folder compressed? Perhaps 2005 does a check against this whe
re 2000 did not?
(Storing database files as compressed files has never been supported.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<roberta.coffman@.emersonprocess.com> wrote in message
news:1137444250.800043.163510@.g14g2000cwa.googlegroups.com...
> The following code which worked just fine with sql2000 sp3 no longer
> works with sql 2005. The error I receive says: "The file filename.mdf
> is compressed but does not reside in a read only database or filegroup.
> The file must be decompressed".
> This happens on the line of code where I wrote <--PROBLEM.
> Can anyone help?
> HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
> databaseDirectoryLocation,CString& error)
> {
> HRESULT hr = NULL;
> LPSQLDMODBFILE pMDFFile = NULL;
> LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
> LPSQLDMODATABASE pDatabase = NULL;
> LPSQLDMOFILEGROUPS pFileGroups = NULL;
> LPSQLDMOFILEGROUP pFileGroup = NULL;
> LPSQLDMODBFILES pDBFiles = NULL;
> LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
> LPSQLDMOLOGFILES pLogFiles = NULL;
> LPSQLDMODATABASES pDatabases = NULL;
>
> CString fileName = dataBaseName + _T(".mdf");
> error = _T("");
> hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
> if (hr == S_OK)
> {
> fileName = dataBaseName + _T(".ldf");
> hr =
> createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
> if (hr == S_OK)
> {
> hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
> CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
> if (hr == S_OK)
> {
> pDatabase->SetName(dataBaseName);
> // add DBFile to DBFiles collection, in PRIMARY FileGroup
> hr = pDatabase->GetFileGroups(&pFileGroups);
> hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
> hr = pFileGroup->GetDBFiles(&pDBFiles);
> if FAILED( hr = pDBFiles->Add(pMDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> hr =
> pDatabase->GetTransactionLog(&pTransactionLog);
> hr = pTransactionLog->GetLogFiles(&pLogFiles);
> if FAILED( hr = pLogFiles->Add(pLDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> // add Database to Database collection, this creates the data and
> log
> //devices and the actual new database
> hr = pSQLServer->GetDatabases(&pDatabases);
> if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
> {
> error = getDMOError(hr);
> }
> }
> }
> }
> else
> {
> error = _T("CoCreateInstance failed in CreateDataBase.");
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> // release all objects
> if (pFileGroups) pFileGroups->Release();
> if (pFileGroup)pFileGroup->Release();
> if (pDBFiles) pDBFiles->Release();
> if (pMDFFile) pMDFFile->Release();
> if (pTransactionLog) pTransactionLog->Release();
> if (pLogFiles) pLogFiles->Release();
> if (pLDFFile) pLDFFile->Release();
> if (pDatabases) pDatabases->Release();
> if (pDatabase) pDatabase->Release();
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////
> HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
> directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
> {
> HRESULT hr;
> error = _T("");
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
> if (hr == S_OK)
> {
> pDBFile->SetName(fileName);
> pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
> pDBFile->SetSize(10); //MB
> pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pDBFile->SetFileGrowth(10);
> pDBFile->SetMaximumSize(100);
> pDBFile->SetPrimaryFile(TRUE);
> }
> else
> {
> error = _T("CoCreateInstance failed in createDBFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////
> HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
> directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
> {
> error = _T("");
> HRESULT hr;
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
> if(hr == S_OK)
> {
> pLDFFile->SetName(logFileName);
> pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
> pLDFFile->SetSize(5);
> pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pLDFFile->SetFileGrowth(10);
> pLDFFile->SetMaximumSize(100);
> }
> else
> {
> error = _T("CoCreateInstance Failed in createLogFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
>|||Databases files were never supported on a compressed drive with 2000 but I
don't think it ever checked for it. It looks like 2005 makes some kind of
check. This is from BOL 2000:
'os_file_name'
Is the path and file name used by the operating system when it creates the
physical file defined by the <filespec>. The path in os_file_name must
specify a directory on an instance of SQL Server. os_file_name cannot
specify a directory in a compressed file system.
Andrew J. Kelly SQL MVP
<roberta.coffman@.emersonprocess.com> wrote in message
news:1137444250.800043.163510@.g14g2000cwa.googlegroups.com...
> The following code which worked just fine with sql2000 sp3 no longer
> works with sql 2005. The error I receive says: "The file filename.mdf
> is compressed but does not reside in a read only database or filegroup.
> The file must be decompressed".
> This happens on the line of code where I wrote <--PROBLEM.
> Can anyone help?
> HRESULT CSQLWrapperApp::CreateDataBase(CString dataBaseName,CString
> databaseDirectoryLocation,CString& error)
> {
> HRESULT hr = NULL;
> LPSQLDMODBFILE pMDFFile = NULL;
> LPSQLDMOLOGFILE pLDFFile = NULL; /*IWSQLDMOLogFile * */
> LPSQLDMODATABASE pDatabase = NULL;
> LPSQLDMOFILEGROUPS pFileGroups = NULL;
> LPSQLDMOFILEGROUP pFileGroup = NULL;
> LPSQLDMODBFILES pDBFiles = NULL;
> LPSQLDMOTRANSACTIONLOG pTransactionLog = NULL;
> LPSQLDMOLOGFILES pLogFiles = NULL;
> LPSQLDMODATABASES pDatabases = NULL;
>
> CString fileName = dataBaseName + _T(".mdf");
> error = _T("");
> hr = createDBFile(fileName,databaseDirectoryL
ocation,pMDFFile, error);
> if (hr == S_OK)
> {
> fileName = dataBaseName + _T(".ldf");
> hr =
> createLogFile(fileName,databaseDirectory
Location,pLDFFile,error);
> if (hr == S_OK)
> {
> hr = CoCreateInstance(CLSID_SQLDMODatabase, NULL,
> CLSCTX_INPROC_SERVER, IID_ISQLDMODatabase, (LPVOID*)&pDatabase);
> if (hr == S_OK)
> {
> pDatabase->SetName(dataBaseName);
> // add DBFile to DBFiles collection, in PRIMARY FileGroup
> hr = pDatabase->GetFileGroups(&pFileGroups);
> hr = pFileGroups->GetItemByName(TEXT("PRIMARY"), &pFileGroup);
> hr = pFileGroup->GetDBFiles(&pDBFiles);
> if FAILED( hr = pDBFiles->Add(pMDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> hr =
> pDatabase->GetTransactionLog(&pTransactionLog);
> hr = pTransactionLog->GetLogFiles(&pLogFiles);
> if FAILED( hr = pLogFiles->Add(pLDFFile) )
> {
> error = getDMOError(hr);
> }
> else
> {
> // add Database to Database collection, this creates the data and
> log
> //devices and the actual new database
> hr = pSQLServer->GetDatabases(&pDatabases);
> if FAILED( hr = pDatabases->Add(pDatabase) ) <-- PROBLEM
> {
> error = getDMOError(hr);
> }
> }
> }
> }
> else
> {
> error = _T("CoCreateInstance failed in CreateDataBase.");
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> }
> else
> {
> hr = S_FALSE;
> }
> // release all objects
> if (pFileGroups) pFileGroups->Release();
> if (pFileGroup)pFileGroup->Release();
> if (pDBFiles) pDBFiles->Release();
> if (pMDFFile) pMDFFile->Release();
> if (pTransactionLog) pTransactionLog->Release();
> if (pLogFiles) pLogFiles->Release();
> if (pLDFFile) pLDFFile->Release();
> if (pDatabases) pDatabases->Release();
> if (pDatabase) pDatabase->Release();
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////
> HRESULT CSQLWrapperApp::createDBFile(CString fileName, CString
> directoryName,LPSQLDMODBFILE & pDBFile, CString& error)
> {
> HRESULT hr;
> error = _T("");
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMODBFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMODBFile, (LPVOID*)&pDBFile);
> if (hr == S_OK)
> {
> pDBFile->SetName(fileName);
> pDBFile->SetPhysicalName(directoryName + _T("\\") + fileName );
> pDBFile->SetSize(10); //MB
> pDBFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pDBFile->SetFileGrowth(10);
> pDBFile->SetMaximumSize(100);
> pDBFile->SetPrimaryFile(TRUE);
> }
> else
> {
> error = _T("CoCreateInstance failed in createDBFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
> //////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////
> HRESULT CSQLWrapperApp::createLogFile(CString logFileName, CString
> directoryName, LPSQLDMOLOGFILE& pLDFFile,CString& error)
> {
> error = _T("");
> HRESULT hr;
> try
> {
> hr = CoCreateInstance(CLSID_SQLDMOLogFile, NULL, CLSCTX_INPROC_SERVER,
> IID_ISQLDMOLogFile, (LPVOID*)&pLDFFile);
> if(hr == S_OK)
> {
> pLDFFile->SetName(logFileName);
> pLDFFile->SetPhysicalName(directoryName + _T("\\") + logFileName );
> pLDFFile->SetSize(5);
> pLDFFile-> SetFileGrowthType(SQLDMOGrowth_Percent);
> pLDFFile->SetFileGrowth(10);
> pLDFFile->SetMaximumSize(100);
> }
> else
> {
> error = _T("CoCreateInstance Failed in createLogFile");
> }
> }
> catch (...)
> {
> }
> return hr;
> }
>|||Roberta, Tibor and Andrew,
Yes, SQL 2005 checks whether the newly created file is compressed.
The folder where the data files reside must not be compressed.
ML
http://milambda.blogspot.com