Hi all,
What are the factors to be considered while desiging a cube..
creating cube through wizard is the way to do it ?
How to determine abt fact and dimension tables..
pls provide me more links to get more idea about Cubes .. which helps me a lot to furthur proceed.
Thanks,
Nav
I m designing a table which will hold about 40 million records.
The data which is going to be inserted into the table is a alphanumeric unique code eg: (CABBXFGRET). Similarly there will be 40M codes, all are indexed starting with character 'C', with the second alphabet used as an index for about 5 million codes each.
Is it advisable to have 1 table and load all the records in it or is it more sensible to split the codes in to various tables based on index.
Eg: All codes starting with 'CA.....' till 'CC.....' in one table. etc.
So that the search logic can be designed based on the index of the code.
Logically considering this is helping us reduce the latency in search due to the heavy volume. But i m concerned that this doesnt follow the rules of normalization.
Any suggestions from the DB gurus out there is very much appeciated.
Thanks
sunil.
I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach
View 2 Replies View RelatedI have a cube. Its xml is different at some point than its design view. Suppose for some dimension and its attributes, source table is different than what it showing in the properties window for them..
Is this possible? How to read cube xml because there are repeating tags in it. There are two type if dimension tags.. one has only attributed and other has all properties.
Hi guys, I am having a new server which carrying 64-bit processor. So SQL server 2005 64-bit is needed and this is my first time to do so. Can I know is there any consideration on having a 64 bit SQL Server 2005 ?
For example,
i ) mirroring between 64 bit principal server with 32 bit mirror server ?
ii) replication between 64 bit server with 32 bit mirror server ?
Hope able to get any advices here. THank you.
Best Regards,
Hans
Hi,
View 6 Replies View Relatedhi
i am using html checkboxes to let user to select categories in which search has to be done. there are 4 checkboxes for 4 categories.
this query i have checked in query anakyzer. this query is working and displaying results.acc to this query user has selected "3 checkboxes" and entered "airports" to search.
SELECT * FROM tbl_Arab_Newsletter WHERE (Month(month) = '2' AND year(month) = '2008') and (cat_number = 1 or cat_number = 2 or cat_number = 3) and (story like '%airports%' )
i have written code like this.
strSQL = "SELECT * FROM tbl_Arab_Newsletter WHERE (Month(month) = '"& m &"' AND year(month) = '"& y &"')"strSQL = strSQL & " and cat_number = 1"
end if
if cat2="on" then 'check box 2 is enabled means this conditionstrSQL = strSQL & " and cat_number = 2"
end if
if cat3="on" then 'check box 3 is enabled means this conditionstrSQL = strSQL & " and cat_number = 3"
end if
if cat4="on" then 'check box 4 is enabled means this conditionstrSQL = strSQL & " and cat_number = 4"
end if
strSQL = strSQL & " and (story like '%" & search & "%')"
but this statements are not working. how to write the if conditon acc to the query. pls help and if dont mind pls give me the correct code.
thank u.
Please suggest correct answer (A,B,C,D) for the following Question
You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York, Chicago, Paris, London, San Francisco, and Tokyo. In order to accommodate a distributed environment, you must ensure that each row entered into the inventory table is unique across all location. How can you create the inventory table?
A.Use the identity function. At first location use IDENTITY(1,4), at second location use IDENTITY(2,4), and so on.
B.Use the identity function. At first location use IDENTITY(1,1), at second location use IDENTITY(100000,1), and so on.
C.CREATE TABLE inventory ( Id Uniqueidentifier NOT NULL DEFAULT NEWID(), ItemName Varchar(100) NOT NULL, ItemDescription Varchar(255) NULL, Quantity Int NOT NULL, EntryDate Datetime NOT NULL).
D.Use TIMESTAMP data type.
Give the suitable answer for the below question and explain the answer.
1.You have designed the database for a Web site (or online ticketing agency) that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor.
For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?
(A). Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.
(B). Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.
(C). Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.
(D). Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
hey can anyone suggest me how to write the efficient( a bit faster) stored proc to generate alternative usernames( with logical variations like the one of hotmail ) if provided one is already present in database... :)
View 5 Replies View RelatedHi,
I have a table with two columns like this.
teacher table
teacher_id
1
2
3
4
5
6
student table
student_id --teacher_id
1 ----------- 10
1 ----------- 11
2 ----------- 12
2 ----------- 13
2 ----------- 14
3 ----------- 15
4 ----------- 16
Now i need the least assigned teacher_id's in the teachers table. i.e i need teacher_id's 5,6.
One possible way of writing the query is given below.
DECLARE @teachers TABLE
(
teacher_idint
)
INSERT INTO @teachers
SELECT1 UNION ALL
SELECT2 UNION ALL
SELECT3 UNION ALL
SELECT4 UNION ALL
SELECT5 UNION ALL
SELECT6
DECLARE @students TABLE
(
teacher_idint,
student_idint
)
INSERT INTO @students
SELECT1 , 10 UNION ALL
SELECT1 , 11 UNION ALL
SELECT2 , 12 UNION ALL
SELECT2 , 13 UNION ALL
SELECT2 , 14 UNION ALL
SELECT3 , 15 UNION ALL
SELECT4 , 16
-- query starts here
SELECT t.teacher_id
FROM @teachers t LEFT OUTER JOIN @students s
ON t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
HAVING COUNT(s.teacher_id) =
(
SELECT TOP 1 COUNT(s.teacher_id)
FROM @teachers t LEFT OUTER JOIN @students s
ON t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
ORDER BY COUNT(s.teacher_id)
)
-- query ends here
But the problem with this query is, i am using two outer joins on the same query and on the same tables....
If teacher table and student tables have few thousands of records, this query will not perform good....
Please suggest another way of writing the query which can perform well....
Thanks in advance
Suresh
I am new in sqlserver UDF,
I am written a UDF but it showing error
CREATE FUNCTION udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
END
RETURN (@rtDayofWeek)
END
GO
Error
Incorrect syntax near '‘'.
Msg 102, Level 15, State 1, Procedure udf_DayOfWeek, Line 16
Incorrect syntax near 'END'.
Error: 0xC02020A1 at Data Flow Task, Source - mysourcefile [1]: Data conversion failed. The data conversion for column "myBadColumn" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Source - mysourcefile [1]: The "output column "myBadColumn" (157)" failed because truncation occurred, and the truncation row disposition on "output column "myBadColumn" (157)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Two questions:
1. How to tell SSIS not to stop for one bad row. Getting the good rows is more important than one bad row.
2. What code page do I need so this will load?
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.
hello all Plz can u suggest m how can i find substring like i want to show first 50 characters and i dont want that my last 50th character should end with full word .. word should be complete it may b 53 or 54 th character for example :m using : substring(Remark,0,50) as Remarkand it returns : this is .................................. boobut i want thi is ....................................book.complete sentense plz suggest me how is this be don in SQL query.thax alot
View 3 Replies View RelatedHello,First of all - sorry for may be stupid question, but as I am not aWin* administrator and my field is *nix, I am a little bit stuck witha problem, presented to me by one of the customers. He has few windowsboxes with some webfile and (most important) - mssql database. And heasks for a backup. I found some company, which offers us IBM's Tivolisoftware, but my opinion is that they want a little bit too much forit and also, from my *nix experiece, I know there should be manyother products, probably cheaper and better. If not - we will usethat Tivoli. So we need to backup mssql data and some filessomewhere. The best would be on some ftp.Can anyone please suggest a good and simple way of making such kind ofbackup?Many thanks,Anton.
View 2 Replies View RelatedHi,
I need you to suggest me one book so that I can learn T-sql - beginner to advanced.
I want to start writing queries like Manivannan and others so that i can also help others
Please suggest and give me a plan. I am ready to sincerely dedicate 2-3 hours every day.
thanks
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]....
Hi,
Can someone suggest me a simple and nice tutorial that can explain T-SQL with examples.
Plizzz....
Regards..,
Aazad
First posting to the group. I have received a lot of valuable info from youguys. Now, an OT question:What's a good tape drive to perform unmanned weekly backups for a WindowsXP Pro box running SQL server 2000?--Joel Farris | AIM: FarrisJoel** Their Web. Your Way. http://getfirefox.com **
View 1 Replies View RelatedI am trying to recreate the same functionality Google has in regards tosuggesting words (not names), when you misspell something it comes upwith suggestions.We have a list of words in the database to match against.I've looked at SOUNDEX but it is not close enough, DIFFERENCE is evenworse.The only way I can get SOUNDEX to be more accurate is withSELECT [word]FROM [tbl_word]WHERE ( SOUNDEX( word ) = SOUNDEX( 'test' ) AND LEN( word) = LEN('test' ) )I've been looking at Regular Expression matching which I reckon wouldprovide more accurate matches. Not sure how that will affectperformance, as we could be talking about 20,000 records.Or also been looking at the Double Metaphone algorithm.Is there something else that I am missing, anyone know what to use in asituation like this?Thanks in advance.
View 1 Replies View RelatedWhen 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 RelatedHi,
our company is looking for a good training for SQL server 2005. Majority of attendies will be .NET developers, but some will be technicians who need backup, replication, maint., etc. training. All are pretty familiar with sql server and have experience with SQL 2000. So, it should not be for beginners. Intermediate and advance topics.
Whom you can suggest? Do you have experience with them?
Thank you.
Victor
See topic
View 1 Replies View RelatedI have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos
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.
I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.
What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)