I have a date dimension and Fiscal Year is a Hierarchy in it, like April, August and October for different fiscal years. I need to populate this in a report (SSRS) in a combo box so that the user can pick the fiscal year he/she wants. When done, I want to know what is the Fiscal year start month is, like April for April Fiscal year etc.
How do I achieve this? Is it even possible?
Good day all
I am unable to crosstab different alternate hierarchies of an MSAS cube from a single dimension, I'm using Cognos Powerplay (7.3 web and client) to browse the data. v 7.1 displays the same behaviour.
When I try to create the crosstab the display replaces both rows and columns to the alternate path, it overwrites the original path. Can anyone tell me if this is a Cognos issue or an Analysis services one and if there is any way around it. The only solution I have at the moment is to bodge it by creating seperate dimensions for each alternate hierarchy however this is ugly and difficult to use.
Regards
Mike
Hi,
I am currently trying to develop an application which would help in retrieving data from cubes (Microsoft Analysis Services)! The user would not be accessing the Business Intelligence Studio, etc. but would be viewing the data from a custom made application developed in VB.Net2005.
While implementing this, I want to populate the drop-down-list in the VB(.Net) Form, by retrieving the various hierarchies in the dimensions of the cubes (along with dimensions if possible). This should be done dynamically and in real-time!
Can you please help in implementing this? Any code/method, etc would be highly appreciated!
Thanks in advance.
Best wishes!
(Software : SQL Server 2005 Enterprise Edition (with Analysis Services and BI Studio), VS.Net 2005 Enterprise Edition, ADOMD.Net)
I am trying to report against a cube and I want the user to be able to choose the date for the report.
Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.
I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?
As promised, question #2... ;)
As I mentioned in my previous post, my "real" case is a cube with 79 dimensions, most of which virtual, have been added for convenience.
Think for instance about a time dimension... Wouldn't it be nice to get a matrix with years horizontally, months vertically and displaying say the number of order you had for each cell in the resulting grid.
Ok, maybe you can do this with MDX but not in Excel, unless you create virtual dimensions for the Year and Month levels.
That's all good, but as it is, in my real case, I end up with four date dimensions for which I have to provide:
YQMD (Year, Quarter, Month & Day) hirarchized dimension
YWD (Year, Week, Day)
YQMD (Fiscal calendar)
Year
Quarter
Month
Week
Day (as 1, .. 31)
Week Day (for periodicity analysis over a week's time)
Date (as individual day - this is the backbone for the virtual dimensions)
It turns out this makes 40 dimensions by itself. For the sake of it, I grouped them by 4 hierarchies, although I've seen no specific functionality off of this in the data browser or Excel, so it really seems to be only for "show".
Now in my previous post I explained how I "spread" my session count to calculate a conversion rate. Given the number of dimensions I have (very high segmentation at the order level, very limited segmentation at the session/visit level), this means my calculated cell formula looks like this (hold your breath, it's ugly):
SubCube: {[Measures].[Session Count]}, DESCENDANTS([Business Unit].[All]), DESCENDANTS([Channel Source].[All]), DESCENDANTS([Currency].[All]), DESCENDANTS([FO Product Segment].[All]), DESCENDANTS([FO Quarter].[All]), DESCENDANTS([FO Value Segment].[All]), DESCENDANTS([Is Cancelled].[All]), DESCENDANTS([Is Customer Care].[All]), DESCENDANTS([Is Design Services].[All]), DESCENDANTS([Is Direct].[All]), DESCENDANTS([Is Estimated].[All]), DESCENDANTS([Is Holiday Order].[All]), DESCENDANTS([Is New].[All]), DESCENDANTS([Is Sales Services].[All]), DESCENDANTS([Language].[All]), DESCENDANTS([Payment].[All]), DESCENDANTS([Product Segment].[All]), DESCENDANTS([Value Segment].[All]), DESCENDANTS([FO Channel].[All]), DESCENDANTS([Reg Channel].[All]), DESCENDANTS([FCD].[Date].[All]), DESCENDANTS([FCD].[Day].[All]), DESCENDANTS([FCD].[Mth].[All]), DESCENDANTS([FCD].[Qtr].[All]), DESCENDANTS([FCD].[Wk].[All]), DESCENDANTS([FCD].[WkDay].[All]), DESCENDANTS([FCD].[Year].[All]), DESCENDANTS([FCD].[Fiscal].[All]), DESCENDANTS([FCD].[YMQD].[All]), DESCENDANTS([FCD].[YWD].[All]), DESCENDANTS([FRD].[Date].[All]), DESCENDANTS([FRD].[Day].[All]), DESCENDANTS([FRD].[Mth].[All]), DESCENDANTS([FRD].[Qtr].[All]), DESCENDANTS([FRD].[Wk].[All]), DESCENDANTS([FRD].[WkDay].[All]), DESCENDANTS([FRD].[Year].[All]), DESCENDANTS([FRD].[Fiscal].[All]), DESCENDANTS([FRD].[YQMD].[All]), DESCENDANTS([FRD].[YWD].[All]), DESCENDANTS([FSD].[Date].[All]), DESCENDANTS([FSD].[Day].[All]), DESCENDANTS([FSD].[Mth].[All]), DESCENDANTS([FSD].[Qtr].[All]), DESCENDANTS([FSD].[Wk].[All]), DESCENDANTS([FSD].[WkDay].[All]), DESCENDANTS([FSD].[Year].[All]), DESCENDANTS([FSD].[Fiscal].[All]), DESCENDANTS([FSD].[YQMD].[All]), DESCENDANTS([FSD].[YWD].[All])
Caculation Value: CalculationPassValue(([Business Unit].[All],[Channel Source].[All],[Currency].[All],[FO Product Segment].[All],[FO Quarter].[All],[FO Value Segment].[All],[Is Cancelled].[All],[Is Customer Care].[All],[Is Design Services].[All],[Is Direct].[All],[Is Estimated].[All],[Is Holiday Order].[All],[Is New].[All],[Is Sales Services].[All],[Language].[All],[Payment].[All],[Product Segment].[All],[Value Segment].[All],[FO Channel].[All],[Reg Channel].[All],[FCD].[Date].[All],[FCD].[Day].[All],[FCD].[Mth].[All],[FCD].[Qtr].[All],[FCD].[Wk].[All],[FCD].[WkDay].[All],[FCD].[Year].[All],[FCD].[Fiscal].[All],[FCD].[YMQD].[All],[FCD].[YWD].[All],[FRD].[Date].[All],[FRD].[Day].[All],[FRD].[Mth].[All],[FRD].[Qtr].[All],[FRD].[Wk].[All],[FRD].[WkDay].[All],[FRD].[Year].[All],[FRD].[Fiscal].[All],[FRD].[YQMD].[All],[FRD].[YWD].[All],[FSD].[Date].[All],[FSD].[Day].[All],[FSD].[Mth].[All],[FSD].[Qtr].[All],[FSD].[Wk].[All],[FSD].[WkDay].[All],[FSD].[Year].[All],[FSD].[Fiscal].[All],[FSD].[YQMD].[All],[FSD].[YWD].[All],[Measures].[Session Count]), 0)
If you read all this, you can see already the cryptic dimension names like "FRD", "FSD" and so forth... that's because with the real names ("First Refund Date", "First Ship Date", the query processor errored out... visibly there is a limit in the size of the formulas you can post!
Is there no other way to achieve this result? Basically I mean to say: if the session count is not defined at your level along this dimension, go to the root of the dimension to get the value there, this along a slew of dimensions, many of which are inherently dependent because of the usage of virtual dimensions (therefore if I wish to go to the root of my "First Refund Date" for instance, I wish to do so along all sub-dimensions... Heck, as far as I'm concerned this is conceptually only ONE dimension, just with various views upon it...
Using hierachies I was sort of hoping for the ability to have something like:
[FRD].[All Hierachies].[All]
Am I just asking for too much or do I just not know (quite probable) the magic keyword that can do this?
Where this is becoming quite critical is that I actually have a calculated cell that goes as follows (abridged):
SubCube: similar as before
Calculation: [Measures].[Order Count]*CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Distributed Marketing Cost])/CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Order Count])
Now the purpose of this is to distribute external costs at the order level. In short, say that you know you spent $10,000 globally promoting a specific group of websites in commission money (you pay $1 for each order). This formula allows me to get that a specific website, with 20 orders incurred an additional marketing cost of $20. That's actually the object of my next thread's question (spreading a multiplication through the aggregations)
For the purpose of this thread I am just concerned about the size of my formulas. Renaming the dimensions seemed to "buy" me some margin and I was actually quite surprised to find that the formula still fitted in, and works, but it is only a matter of time until I have to add more dimensions and the whole thing blows up in my face. Adittionally this is obviously not pleasant to look at and maintain.
Any better solution?
Thanks,
Seb
Hi,
Nwebie question:
is it possible to have a fact that will behave diffeerntly as the user
is viewing it from different hierarchies? say i got creatin measures
of a product inventory. i got a location hierarchy, as as user is
'moving up' in it (say,up by city, region, state, country...)i want to
do a sum. but i also got time hierarchy, and as user begins to group
by weeks, months, years... a sum makes no sense..i want LastNonEmpty
or something like that. Is it possible?
thanks!
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.
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
parent | child
1 | 2
2 | 3
2 | 4
3 | 6
3 | 7
4 | 8
5 | 9
8 | 10
8 | 11
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Thanks.
Jon
Hi SQL Server Experts,
Oracle has a 'Connect by prior' extension to SQL that is neat for display hierarchies.
Does SQL Server have anything simialr or would I use T-SQL or ???
I will appreciate any suggestions and advice.
B.Dimple
Junior DBA
i guess really i'm looking for opinions and / or experiences of modelling organisational hierarchies.... anyone?
i'm in the process of creating a logical model (very early stages) of a new datawarehouse. The current OLTP schema stores it as a self referencing key (i.e. parent_group_id). The performance problems involved in aggregating to different levels of the hierarchy causes no end of complaints from customers, as i'm sure you can imagine. So, now we are modelling a datawarehouse for their reporting requirements, i obviously want it to be better
so far i've considered...
1) keeping it as it is
2) allocating a 'node' id and storing the left / right nodes in the hierarchy tree. we've used before when reporting over the OLTP system with report developers building it 'on the fly' as part of their report.
(better explanation than mine... http://www.dbpd.com/vault/9811/kamfn.shtml )
3) an intermediate table storing its 'position' in the hierarchy? not really played with this yet but mr kimball seems to like it
see... http://www.dbmsmag.com/9809d05.html
4) denormalising it completely. i.e. level_1, level_2, level_3, Level4.... for every fact? the advantage of this is that it makes aggregation at any level really easy. the problem is that each of our customers has a different organisation hierarchy, with a different number of levels, and given the OLTP schema there is no limit to the number of levels in a hierarchy
so... thoughts / experiences anyone?
Em
Is there a good approach to modelling many heterogeneous entity typeswith that have some attributes in common?Say I have entities "employees" which share some attibutes (e.g.firstname, lastname, dateofbirth) but some subsets of employees (e.g.physicians, janitors, nurses, ambulance drivers) may have additionalattributes that do not apply to all employees. Physicians may haveattributes specialty and date of board certification, ambulancedrivers may have a drivers license id, janitors may havepreferredbroomtype and so on.There are many employee subtypes and more can be dynamically addedafter the application is deployed so it's obviously no good to keepadding attributes to the employees table because most attributes willbe NULL (since janitors are never doctors at the same time).The only solution I found for this is a generalization hiearchy whereyou have the employee table with all generic attributes and then youadd tables for each new employee subtype as necessary. The subtypetables share the primary key of the employee table. The employee tablehas a "discriminator" field that allows you to figure out whichsubtype table to load for a particular entity.This solution does not seem to scale since for each value of"discriminator" I need to perform a join with a different table. Whatif I need to retrieve 1,000 employees at once?Is that possible to obtain a single ResultSet with one SQL statementSQL?Or do you I need to iterate look at the discriminator and thenperform the appropriate join? If this kind of iteration is necessarythen obviously this generalization hierarchy approach does not work inpracticesince it would be painfully slow.Is there a better approach to modelling these kind of heterogeneousentities with shared attributes that does not involve creating a tablefor each new employee type or having sparce tables (mostly filled withNULLS)I guess another approach would be to use name/value pairs but thatwould make reporting really ugly.Seems like a very common problem. Any ideas? Is this a fundamentallimitation of SQL?Thanks!- robert
View 13 Replies View RelatedI 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]....
I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.
For example I have data like
H1 H2 Amount
1 1 100
2 1 50
3 1 45
I am getting a value of 100+50+45=195.
I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...
H1 H2 Amount
1 1 100
2 1 50
I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager, Delivery Manager).
One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child has more than 1 parent).
Are there other more efficient ways? What is the best way to implement this?
I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.
It appears that MS have assumed that SSIS users will automatically use SSAS.
We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.
I would like to be able build multi-level dimension hierarchies directly from within SSIS.
Has MS considered this for future versions?
I've built a robust looking Calendar dimension but that's easy because its all so well structured. Every date belongs to a particular week and every week belongs to a particular month and every month belongs to a particular quarter etc. January 1st is always going to be a member of week 1, month 1, quarter 1.But here's the rub, real life isn't as predictable and stable as a calendar.
Lets say you have an table containing personal information. You might have gender and marital status in there and even though they both relate to one person, they're unrelated to each other so how do you put those in a hierarchy? Which one goes first and which second because you can get combinations of either.
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 am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as
For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with
member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
Can any body help me to know about cube in dataminig?
How is the cube represented in database?
How to querry the cube?
unable to browse the cube - unspecified error - ms sql server
--- even the sps are up to date..
what is the missing link...
thank u
I have an archieve cube as .cab file and i have restored my cube into analysis server , but i am not able to browse the datain the cube. My doubt is- Does the cube contains data with it to browse or not? or we have to explicitly enter data at the sql query analyser.
Thanks in advance.
Hi ALL,
Just started researching Analysis Services product, and am playing w/ foodmart db (located on my C: drive) and creating cubes and dimensions, etc.
My problem is that I cannot browse any dimensions once they are created. For exampe, for a dimension called product I am getting an error message, "Unable to browse the dimension 'product'. Unspecified error"
Can anyone offer help?
thanks so much,
Alan
1.Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.
2.Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a.IS NULL-
b.GROUPING-
c.AS-
3.Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
4.Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a.A CUBE query with Grouping used to distinguish Null values.
b.A CUBE query showing a multidimensional cube.
c.A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.
5.Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.