473,837 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating a new partition in an OLAP database

I am trying to write a C# backend program that will handle the
database maintenance that I would otherwise have to do via Microsoft's
Analysis Services front end GUI Wizards. Currently I am having a
problem with recreating the SliceValue. The value is a time dimension
and should equate to [Monthly Dates].[Year].[Quarter].[Month] and
instead it is these same values, but instead of being seperated by
periods it has some sort of non-displayable character (maybe a
newline) that is placing each value on a new line. The error I
receive when I attempt to process the partition is as follows:

An unhandled exception of type
'System.Runtime .InteropService s.COMException' occurred in
ProcessCubes.ex e

Additional information: Processing error [Object does not exist]
'[All]
[2003]
[Quarter 2]
[May]'

The code is below.

public void ProcessCubeIncr emental(string ServerName, string
DatabaseName, string Frequency, string CubeName, string FactTableName,
string ReportDate)
{
DSO.ServerClass dsoServer = new DSO.ServerClass ();
DSO.MDStore dsoDatabase;
DSO.MDStore dsoCube;
DSO.MDStore dsoPartitionNew ;
DSO.MDStore dsoPartitionOld ;
DSO.Dimension dsoDimension;
DSO.Level dsoLevel;
//DSO.Level dsoLevelAll;
//DSO.Level dsoLevelYear;
//DSO.Level dsoLevelQuarter ;
//DSO.Level dsoLevelMonth;
DSO.Property dsoProperty;
DateTime dt = Convert.ToDateT ime(ReportDate) ;
GregorianCalend ar c = new
GregorianCalend ar(GregorianCal endarTypes.Loca lized);
int YearOld = c.GetYear(c.Add Months(dt,-1));
int MonthOld = c.GetMonth(c.Ad dMonths(dt,-1));
string YearOldStr = YearOld.ToStrin g();
string MonthOldStr;
if (MonthOld < 10)
{
MonthOldStr = "0"+MonthOld.To String();
}
else
{
MonthOldStr = MonthOld.ToStri ng();
}
int YearNew = c.GetYear(dt);
int MonthNew = c.GetMonth(dt);
string YearNewStr = YearNew.ToStrin g();
string MonthNewStr;
if (MonthNew < 10)
{
MonthNewStr = "0"+MonthNew.To String();
}
else
{
MonthNewStr = MonthNew.ToStri ng();
}

switch(MonthNew )
{
case 1:
MonthNewName = "January";
break;
case 2:
MonthNewName = "February";
break;
case 3:
MonthNewName = "March";
break;
case 4:
MonthNewName = "April";
break;
case 5:
MonthNewName = "May";
break;
case 6:
MonthNewName = "June";
break;
case 7:
MonthNewName = "July";
break;
case 8:
MonthNewName = "August";
break;
case 9:
MonthNewName = "September" ;
break;
case 10:
MonthNewName = "October";
break;
case 11:
MonthNewName = "November";
break;
case 12:
MonthNewName = "December";
break;
}

string PartitionNameOl d = "p"+YearOldStr+ MonthOldStr;
string PartitionNameNe w = "p"+YearNewStr+ MonthNewStr;
string ReportDateStart = MonthNewStr+"/1/"+YearNewSt r;
string ReportDateRange ;
string ReportDateEnd =
MonthNewStr+"/"+c.GetDaysInMo nth(YearNew,Mon thNew).ToString ()+"/"+YearNewSt r;
if (Frequency == "Daily")
{
ReportDateRange = "between '" + ReportDateStart + "' and '" +
ReportDateEnd + "'";
}
else
{
ReportDateRange = "= '" + ReportDateEnd + "'";
}
// Connect to the local server
dsoServer.Conne ct(ServerName);

// Set up the MDStore objects:
// database, cube, and partition.
dsoDatabase = (DSO.MDStore) dsoServer.MDSto res.Item(Databa seName);
dsoCube = (DSO.MDStore) dsoDatabase.MDS tores.Item(Cube Name);
dsoPartitionOld = (DSO.MDStore)
dsoCube.MDStore s.Item(Partitio nNameOld);
// delete the partition if it already exists
if (dsoCube.MDStor es.Find(Partiti onNameNew) == true)
{
dsoCube.MDStore s.Remove(Partit ionNameNew);
}

// Create a new partition.
dsoPartitionNew = (DSO.MDStore) dsoCube.MDStore s.AddNew("~temp ",
DSO.SubClassTyp es.sbclsRegular );

// Clone the properties from the desired partition to the new
partition.
dsoPartitionOld .Clone(dsoParti tionNew,
DSO.CloneOption s.cloneMajorChi ldren);

// Change the partition name from "~temp" to the name intended for
the new partition.
dsoPartitionNew .Name = PartitionNameNe w;
dsoPartitionNew .AggregationPre fix = PartitionNameNe w+"_";

//dsoPartitionNew .SourceTable = "\"dbo\".\""+Fa ctTableName+"\" ";
dsoPartitionNew .SourceTable = "\""+FactTableN ame+"\"";

// Estimate the rowcount from the reference partition.
dsoPartitionNew .EstimatedRows = dsoPartitionOld .EstimatedRows;

dsoPartitionNew .SourceTableFil ter = dsoPartitionNew .SourceTable +
".\"ReportDate\ " " + ReportDateRange ;

// Set the FromClause and JoinClause properties of the new
partition.
//dsoPartitionNew .FromClause =
dsoPartitionOld .FromClause.Rep lace(dsoPartiti onOld.SourceTab le,
dsoPartitionNew .SourceTable);
//dsoPartitionNew .JoinClause =
dsoPartitionOld .JoinClause.Rep lace(dsoPartiti onOld.SourceTab le,
dsoPartitionNew .SourceTable);

// Change the definition of the data Slice used by the new
partition.
// Change the SliceValue properties of the affected levels and
dimensions to the correct values.
switch(MonthNew )
{
case 1:
case 2:
case 3:
Quarter = 1;
break;
case 4:
case 5:
case 6:
Quarter = 2;
break;
case 7:
case 8:
case 9:
Quarter = 3;
break;
case 10:
case 11:
case 12:
Quarter = 4;
break;
}

// Change the definition of the data slice used by the new
// partition, by changing the SliceValue properties of the
// affected levels and dimensions to the desired values.
dsoDimension = (DSO.Dimension)
dsoPartitionNew .Dimensions.Ite m("Monthly Dates");
dsoDimension.Cu stomProperties. Clear();
//dsoDimension.Cu stomProperties. Remove("SliceVa lue");
//dsoDimension.Cu stomProperties. Add("[All].["+YearNewSt r+"].Quarter
"+Quarter.ToStr ing()+"].["+MonthNewS tr+"]","SliceValue", VBA.VbVarType.v bString);
//dsoLevels = (DSO.Level)
dsoDimension.Le vels.Item("(All ).Year.Quarter. Month");
//dsoLevels.Slice Value = "[All].["+YearNewSt r+"].[Quarter
"+Quarter.ToStr ing()+"].["+MonthNewName+ "]";
//dsoProperty = (DSO.Property)
dsoDimension.Cu stomProperties. Add("[All]","(All)",VBA.V bVarType.vbStri ng);
//dsoProperty = (DSO.Property)
dsoDimension.Cu stomProperties. Add("["+YearNewSt r+"]","Year",VBA.Vb VarType.vbStrin g);
//dsoProperty = (DSO.Property)
dsoDimension.Cu stomProperties. Add("[Quarter
"+Quarter.ToStr ing()+"]","Quarter",VBA .VbVarType.vbSt ring);
//dsoProperty = (DSO.Property)
dsoDimension.Cu stomProperties. Add("["+MonthNewName+ "]","Month",VBA.V bVarType.vbStri ng);
dsoLevel = (DSO.Level) dsoDimension.Le vels.Item("(All )");
dsoLevel.SliceV alue = "[All]";
dsoLevel = (DSO.Level) dsoDimension.Le vels.Item("Year ");
dsoLevel.SliceV alue = "["+YearNewSt r+"]";
dsoLevel = (DSO.Level) dsoDimension.Le vels.Item("Quar ter");
dsoLevel.SliceV alue = "[Quarter "+Quarter.ToStr ing()+"]";
dsoLevel = (DSO.Level) dsoDimension.Le vels.Item("Mont h");
dsoLevel.SliceV alue = "["+MonthNewName+ "]";
//dsoLevelAll = (DSO.Level) dsoDimension.Le vels.Item("(All )");
//dsoLevelAll.Sli ceValue = "[All]";
//dsoLevelYear = (DSO.Level) dsoDimension.Le vels.Item("Year ");
//dsoLevelYear.Sl iceValue = "["+YearNewSt r+"]";
//dsoLevelQuarter = (DSO.Level)
dsoDimension.Le vels.Item("Quar ter");
//dsoLevelQuarter .SliceValue = "[Quarter "+Quarter.ToStr ing()+"]";
//dsoLevelMonth = (DSO.Level) dsoDimension.Le vels.Item("Mont h");
//dsoLevelMonth.S liceValue = "["+MonthNewName+ "]";

// Save the partition definition in the metadata repository.
dsoPartitionNew .Update();

// Process the temporary partition.
//dsoCube.LockObj ect(DSO.OlapLoc kTypes.olapLock Process, "Processing
object...");
dsoPartitionNew .Process(DSO.Pr ocessTypes.proc essFull);

//dsoCube.UnlockO bject();

// Close the allocated resources and exit the subroutine.
dsoCube = null;
dsoDatabase = null;
dsoServer.Close Server();
dsoServer = null;
}

Please help me, I'm a desparate man!
Nov 15 '05 #1
1 5690
jagan
1 New Member
Hi Mark,

This is Jagan. I too had a similar problem. I don't know whether u solved it or not.
But it has a solution. Don't add slices one by one to the dimension. Instead use custom properties of the dimension. Here is the code snippet:

dsoDimension.Cu stomProperties. Clear();
dsoDimension = (DSO.Dimension) dsoPartitionNew .Dimensions.Ite m("dimTime");
dsoDimension.Cu stomProperties. Add("All dimTime.2006.Qu arter 2.April.19", "DimensionSlice ", VBA.VbVarType.v bString);
dsoDimension = (DSO.Dimension) dsoPartitionNew .Dimensions.Ite m("dimEntities" );
dsoDimension.Cu stomProperties. Add("All dimEntities.ITC .ITC", "DimensionSlice ", VBA.VbVarType.v bString);

dsoPartitionNew .Update();
dsoPartitionNew .Process(DSO.Pr ocessTypes.proc essFull);

This solved my problem.

Regards,

Jagan
Jul 29 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2357
by: Syed Mudussir Husain | last post by:
Hello, I am doing a very large web based database project. Over the period of 5 years, "more than 100, 000 Books" will be stored in a Database along with a LARGE amount of book attributes majorly consists of very large amount of people reviews about a book. We then need to analyze or generate reports from that large volume of STATIC data in Infinite number of ways.
7
3584
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowing that to the backwoods manager who knows little of technology that new innovative names for old concepts would help to sale their products. I mean...
6
6889
by: Dim St Thomas | last post by:
I am a developer working on a database client program. I am testing this program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk) This machine has Oracle 9.2.0.1.0 and RedBrick database software installed. I am testing the software by creating small test databases. If I create an Oracle database using the Database Configuration Assistant, it takes forever just to create the database. If I choose to create a new database...
7
2246
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowing that to the backwoods manager who knows little of technology that new innovative names for old concepts would help to sale their products. I mean...
5
2457
by: Framework fan | last post by:
Hello, If I wrote the next ebay (yes I know, yawn-snore) and I had a database with 5 million auction items in it, what would be a really good strategy to get a search done very quickly? Would it involve something called OLAP and/or "data mining"? The only technology I am familiar with is simply SQL Server databases with stored procedures. I think I'd be guessing correctly and say that this technology simply wouldn't be fast enough *on...
0
1690
by: YellowFin Announcements | last post by:
Yellowfin Reporting Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin, today announced the availability of Yellowfin Release 3, the newest version of the leading query, reporting, and analysis tool for the web. Release 3 provides web access to all major relational and online analytical processing (OLAP) sources,...
0
1542
by: YellowFin | last post by:
Yellowfin Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin, today announced the availability of Yellowfin Release 3, the newest version of the leading query, reporting, and analysis tool for the web. Release 3 provides web access to all major relational and online analytical processing (OLAP) sources, intelligent...
7
2854
by: Gladiator | last post by:
Hai all, I have Db2 installed in a partition environment . There are 4 partitons on which i created the instance. can any one tell me if i can create a database on the required partitons ...... that is i want to create the db on 1,3,4 partitons.
1
4967
by: Laurence | last post by:
Hi folks, As I konw: database partition (aka data partition?), the database can span multiple machines; table partition, the data within a table can seperate by certain condition. How about inter-partition and intra-partition? Is inter-partition database partition...?
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10575
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10628
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7004
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4475
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.