I need to create a new partition on a Cube using T-SQL and I am not much aware of either the Cubes or the ActiveX script. I am writing a T-SQL script for creating this partition on a cube.
Select Case iMonth
Case 1,2,3
sQuarter = "1"
Case 4,5,6
sQuarter = "2"
Case 7,8,9
sQuarter = "3"
Case 10,11,12
[Code] .....
We have a MS-OLAP cube that has about 11 partitions and I have created a prototype package which processes these partitions conditionally based on expressions that are fed values from a SQL Server control table. It appears that one or more of the partitions seem to fail due to the fact that all of the data for the various partitions come from the same huge fact table. Is there a way to control the level of concurrency within the package itself? If not, I am thinking I should move some of the partitions to process based on other partitions completing their process successfully. Appreciate any help.
View 2 Replies View RelatedCan we create the Partition on Existing Table?e.g Create table t ( col1 number(10,0), Col2 Varchar(10)) ;After the table Creation can we alter the table to partition the table.
View 2 Replies View RelatedIs it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
Hi All,I want to create a cube using MSSQL2000 i dont know any thing how tocreate and what is parameter required so if any body help me i will bethankfull to him/her.For information : i have 4 tablescustdetailtable which contain all about a customer as per examplecustomer ID,name,add,CUSTOMERID IS PRIMARYKEY,Proddetail table which contain all about a product meanproductcode,productname,productvalue,dman (dateof manufacture),dexpdate of expire,etc etc PRODUCTCODE IS PRIMARY.Sales this table contain data aboutsale mean sales detailcusomerid,productcode,noofpieces,priceperunit,prod uct name,date ofsale. etc etc SALEID IS PRIMARYKEY,PRODUCTCODE AND CUSTOMERID ISFOREIGNKEY AND PRODUCTCODE IS PRIMARYKEY,and atlast i have a table name stok detail which contain productid,DTOFman and DTofexpire and amount of producthere product id,DTOFman and DTofexpire are foreign key.
View 3 Replies View Relatedcan i create a cube by VB.net?
I wanna a dynamic Cube, so I can not define it in the Analysis Services Project.
So the problems come.
I must deploy and process and compile the cube.
That can be done?
thank you.
Hi All,
i got a errors while creating a cubes. error comes like
Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Fact_Trng, Column: fct_trng_per_from_key, Value: 5/25/2006. 0 0
could u please solve thisa problem
Hi experts,
I want to create a cube using sql2005. I have read some articles which told me to create it through Visual Studio and still i cannot find where i should do that. I cannot find an "Analysis Manager" UI in sql2005 or Visual Studio. If any body could help i will really appreciate. Thanks
Can anybody tell me how to create a Virtual Cube from two or more diffrent SSAS 2005 Cubes?
View 7 Replies View RelatedI need to grant create cubes to developers, but if I include them into server administration role, then they could delete or modify all the databases.
Each developer require full administration on their own databases, but not other developer's databases.
I am new in SQL sever Analysis Cube. Can anyone help how to create Analysis server cube in sql server 2000/2005 by with some sample that clarifies how to do what I want to do, or give me some direct advice?
Thanks
Pravat Sharma
i want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!
View 2 Replies View Related
Hi,
I am currently using SSIS ,MS Sql Server 2000 database and 2000 Analysis Services for the cube.
I am creating a new table everyday and giving name like day_20080504, day_20080505 etc...
So then I go to Analysis Services and process dimensions(incremental) AND
Create a new partition using old partition as a template.
My first question is how to create a new partition everday and use old partition as template...(Almost same except database table)
My Second question : Can I do this on 2000 Analysis services or Should I convert my cube into SSAS?
thanks,
J
I have a big problem to solve:
I use MsSqlServer 2000, Analysis service. I have cubes and virtual cubes. I have a dimension of consult, they have a hierarchy that change every month. for example: In june the consult "John" is in D11, in September he is on D14,...
Hierarchie: D10 -> D11, D12, D13 - > C1, C2..
For this reason when i want analysis The consult John in a year(12 months) my result is wrong. With this changes in the hierarchie my analysis become wrong!
I thought in the end of every month store the cube in a local cube, like a picture of every consult, with his sales e others measures. With this i can analysis the consult in a specific month rightly, but i need the anlysis in a year. And in a year i would have 12 local cubes, then i could acess them and do a right analysis. How could i access 12 local cubes? Can i create a virtual cube with this 12 local cubes? Is there another solution for this problem?
Thanks,
Leonardo
BI Analyst
Greetings,
Here's what I need to do. I need to programmatically create offline cubes for multiple customers. Using VS2005 (C#) and SQL Server 2000, I am able to do this using the CREATE GLOBAL CUBE command. However, I need to create cubes from SQL Server 2005 as we have upgraded and the CREATE GLOBAL CUBE command does not work as it did before. I've search and found examples using AMO, but they create a server cube. I already have a server cube.
I need to be able to create filtered offline cubes from the existing server cube. I've also read much on XMLA, but that doesn't do it for me either. The code below creates an empty offline cube. However, is there a way to use this to create a cube with data?
I am creating this in a windows application.
Server server = new Server();
server.Connect(@"Data Source=C:MyLocalCube.cub");
server.Disconnect();
Can anyone help? This is driving me nuts!
Can I create a cube file, .cub ,without the Microsoft Analysis Services
Pls Guide me as I am new in this field.
Thanks
Loyd
hi,
I'm creating local cube with Delphi. On my server with MSSQL it work well, but i need to know, which parts of MSSQL is needful to create this local cube (on the server) if i will creat and instal new server with MS SQL.
Thanks for reply.
creating MOLAP, ROLAP and HOLAP cubes... I'm looking for detailed steps involved.I'm using MS-SQL Server 2014.
View 2 Replies View RelatedAlthough I can create the local cube, the cube that is created is very large because it brings over all accounts for the employee. In AS2K, it filtered automatically and only returned accounts that had revenue, which is what I'm trying to do using AS2005. I've read some of the responses about using XMLA and AMO, but I am not sure how to use these within the VS2005 windows application that I use to create and distribute the local cubes. Does anyone have a good example of how to use XMLA, AMO or anything else that would allow me to programmatically create and filter offline cubes using C#?
For instance, this code DOES create an empty cube. But how would I go about getting data into this cube?
Server server = new Server();
server.Connect(@"Data Source=c:MyLocalCube.cub");
server.Disconnect();
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter.
The following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension is cascaded to year- quarter- month.
So, I would like to create a cascading parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
Hi ,
Please tell me how to create Data Source and Data Source View in Report Model Project for OLAP Cube.Whats the provider to use while creating Data Source in Report Model Project to make connection to OLAP Cube.
Thanks
Raghava
Mr. Pearson,
I am currently reading your article titled, "MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist". This article is directly applicable to a problem we are currently trying to solve regarding ragged hierarchies as input parameters.
I have not read the entire article through but am in the process of doing so. Also, I will be trying to implement your solution.
I have one question, will SSRS support multi selection when using a hierarchical picklist? Thanks in advance for any assistance.
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies View Relatedi have a table named table1 sitting on a a partittion scheme px.
table1 is partitioned on u_id by tens,
first partion u_id=0 to 9
second partion u_id=10 to 19
third partion u_id=20 to 29
ans so on and so forth
i have a table named table2 with
records having u_id = 13 to 16
obviously belonging to the second partion.
i want to load table2 to table1 as fast as i could with
the following requirements:
all contents of partition 2 of table1 must be deleted before loading
table2 ceases to exist after the operation
how will i do that.
thanks.
Hi All,
Can someone take a look at my code and tell me what i'm doing in wrong. The script runs fine but when i go to table property it says the table is not partitioned. Thanks for your help.
create database [mypartition]
go
--CREATE FILEGROUP
USE [mypartition]
GO
ALTER DATABASE mypartition ADD FILEGROUP Y2000_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2001_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2002_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2003_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2004_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2005_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2006_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2007_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2008_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2009_filegroup
--CREATE FILES
USE mypartition
GO
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2000,
FILENAME = 'F:ss_datadatadetail_2000.ndf',
SIZE = 2MB)
TO FILEGROUP Y2000_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2001,
FILENAME = 'F:ss_datadatadetail_2001.ndf',
SIZE = 2MB)
TO FILEGROUP Y2001_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2002,
FILENAME = 'F:ss_datadatamdetail_2002.ndf',
SIZE = 2MB)
TO FILEGROUP Y2002_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2003,
FILENAME = 'F:ss_datadatadetail_2003.ndf',
SIZE = 2MB)
TO FILEGROUP Y2003_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2004,
FILENAME = 'F:ss_datadatadetail_2004.ndf',
SIZE = 2MB)
TO FILEGROUP Y2004_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2005,
FILENAME = 'F:ss_datadatadetail_2005.ndf',
SIZE = 2MB)
TO FILEGROUP Y2005_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2006,
FILENAME = 'F:ss_datadatadetail_2006.ndf',
SIZE = 2MB)
TO FILEGROUP Y2006_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2007,
FILENAME = 'F:ss_datadatadetail_2007.ndf',
SIZE = 2MB)
TO FILEGROUP Y2007_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2008,
FILENAME = 'F:ss_datadatadetail_2008.ndf',
SIZE = 2MB)
TO FILEGROUP Y2008_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2009,
FILENAME = 'F:ss_datadatadetail_2009.ndf',
SIZE = 2MB)
TO FILEGROUP Y2009_filegroup;
--CREATE PARTITION FUNCTION
USE [mypartition]
GO
CREATE partition FUNCTION detail_part_function (varchar(10)) AS
RANGE LEFT FOR VALUES('2001','2002','2003','2004','2005','2006','2007','2008')
GO
--CREATE PARTITION SCHEME
USE [mypartition]
GO
CREATE PARTITION SCHEME detail_part_scheme AS
PARTITION detail_part_function TO
(Y2000_filegroup, Y2001_filegroup,Y2002_filegroup,Y2003_filegroup,Y2004_filegroup,Y2005_filegroup,Y2006_filegroup,Y2007_filegroup,Y2008_filegroup,Y2009_filegroup)
GO
-- Now just create a table that uses the particion scheme
USE [mypartition]
GO
/****** Object: Table [dbo].[partitioned_table] Script Date: 05/14/2008 09:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[partitioned_table](
[id] [int] NOT NULL,
[fiscal_year] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_partitioned_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON detail_part_scheme(fiscal_year)
GO
SET ANSI_PADDING OFF
I can see lot of documentation on Range Partitioning. Is there any other type of partition supported in SQL Server 2005?
For example, I have a Fact table having Billion rows. It has a column called BATCH_ID. A BATCH_ID corresponds to 10-20 Million rows and it is a running sequence number like 1,2,3 etc. (not an identity column). Is there anyway I can specify a partition function with BATCH_ID column as an int value? Will the SQL Server automatically does the partition on each int value in that case? If not, what is the best way to do it?
Thanks in advance for all help in this
Hi,
I recently installed MSSQL 2000 and sp3a onto a windows 2003 server in a test lab. I configured one big c: partion on this os and installed the db in the default location.
I need to detach the db's on this server and re-attach them onto another MSSQL 2000/sp3a server running 2003 os with a partition scheme like this:
c: = 20 gigs for the os
e: = 600 gigs for the data
I could not re-attach the db's onto the e:default path odatabase
Is there a work-around for this? This makes sense to me as to why it is not working and was an install oversight on my part but there has to be a way to overcome this delima?
Thanks in advance,
damnit
Hi
Please help me how to do the Horizontal table partition??
I have to split the table in to multiple sub tables with same columns and less rows and then I have to use each sub table.
Thanks you in advance....
Regards
LakshmiPK
Hi everybody
Does anyone know where I can find code to PROCESS a Partition using DSO.
Thank a lot.
I keep getting this error:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'over'.
What am I missing? The max() over statement looks just like the statement in the documentation.
select RegistrationId,
OrderId,
Sequence,
Title,
InformalFirstName,
FirstName,
MiddleName,
Lastname,
EntryDate,
max(Sequence) over(partition by RegistrationId) as 'maxsequence'
from registration
where OrderId = '68379449583' and
Year = '2008' and
Active = 'Yes'
I have a situation where my SQL works everywhere else but my COBOL compiler complains wherever I use PARTITION BY. I can't find a workaround for that problem so I would like to remove all the PARTITION BYs. I'm not confident that I can do this accurately and would like some help getting started.
Here is my simplest example:
SELECT FESOR.REGION, FESOR.TYPE,
COUNT(*) OVER (PARTITION BY FESOR.REGION, FESOR.TYPE)
FROM FESOR, FR
where FESOR.phase = 'Ref'
and FESOR.assign is null
and FESOR.comp_date is null
and FESOR.region = FR.REGION
and FESOR.type = FR.TYPE
and FR.REP_ROW='A'
GROUP BY FESOR.REGION, FESOR.TYPE
What I'm looking for is a modified version of the SQL above which returns the same result set without using PARTITION BY.
Thanks in advance for your assistance.